SQL Query to concatenate column values from multiple rows in Oracle

With SQL model clause:

SQL> select pid
  2       , ltrim(sentence) sentence
  3    from ( select pid
  4                , seq
  5                , sentence
  6             from b
  7            model
  8                  partition by (pid)
  9                  dimension by (seq)
 10                  measures (descr,cast(null as varchar2(100)) as sentence)
 11                  ( sentence[any] order by seq desc
 12                    = descr[cv()] || ' ' || sentence[cv()+1]
 13                  )
 14         )
 15   where seq = 1
 16  /

P SENTENCE
- ---------------------------------------------------------------------------
A Have a nice day
B Nice Work.
C Yes we can do this work!

3 rows selected.

I wrote about this here. And if you follow the link to the OTN-thread you will find some more, including a performance comparison.

There are a few ways depending on what version you have - see the oracle documentation on string aggregation techniques. A very common one is to use LISTAGG:

SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;

Then join to A to pick out the pids you want.

Note: Out of the box, LISTAGG only works correctly with VARCHAR2 columns.

There's also an XMLAGG function, which works on versions prior to 11.2. Because WM_CONCAT is undocumented and unsupported by Oracle, it's recommended not to use it in production system.

With XMLAGG you can do the following:

SELECT XMLAGG(XMLELEMENT(E,ename||',')).EXTRACT('//text()') "Result" 
FROM employee_names

What this does is

• put the values of the ename column (concatenated with a comma) from the employee_names table in an xml element (with tag E)
• extract the text of this
• aggregate the xml (concatenate it)
• call the resulting column "Result"