1

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?

  • 1
    What version of Oracle? Oracle 11g has listagg which can use order by. – Taryn 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. – Taryn 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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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