Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's say I have 3 columns: p_id, description, order_by. I am trying to do the following:

I would like to concatenate the description for all like p_id values. So we are talking a group by p_id sort of thing. But then, I want the description to be concatenated in the order of the order_by column (which is an integer). So my ideal query (not-working) would look like

select p_id, wm_concat(description)
from my_table
where p_id = 12345
group by p_id
order by order_by asc

How do I concatenate rows in this fashion?

share|improve this question
1  
What version of Oracle? Oracle 11g has listagg which can use order by. –  bluefeet Jun 4 '13 at 19:42
    
@bluefeet - Yes I do, I guess I have never used it before. Now, I have figured out how to manipulate the above to use listagg instead of wm_concat. The new problem I get is a ORA-01489 result of string concatenation is too long. Gotta look into this before I can share my final query. –  demongolem Jun 4 '13 at 19:49
    
Oh my, 4000 chars is all I get? Well my concatenation is definitely longer than that. –  demongolem Jun 4 '13 at 19:51
    
String aggregates can only be 4000 bytes, see this answer on StackOverflow about other options. –  bluefeet Jun 4 '13 at 19:58
    
@bluefeet Yeah thanks a bunch. I think that is the sort of approach we are going to have to go with. I am going to write an answer and you can write an answer if you like. –  demongolem Jun 4 '13 at 20:08

1 Answer 1

up vote 0 down vote accepted

So the query (which would work if under 4000 bytes) is

select p_id, listagg(description, '\n') WITHIN GROUP (ORDER BY order_by)
from my_table
where p_id = 12345
group by p_id
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.