Oracle Need multirow data in a single column

Question

I have table like this

 COLA    COLB
  A       1
  A       2
  A       3
  B       12
  B       6
  C       8

I want output like below

 COLA      COLB
   A        1|2|3
   B        12|6
   C        8

Please help me!

Answer 1

You can use listagg():

select cola, listagg(colB, '|') within group (order by NULL)
from table t
group by cola;

The order by NULL specifies no particular order, but it doesn't guarantee ordering. You should have an explicit column for that, if the order makes a difference.

EDIT:

In earlier versions of Oracle, you can use wm_concat():

select cola, replace(wmsys.wm_concat(colB), ',', '|')
from table t
group by cola;

Answer 2

Try this. This works for Oracle 9i above.

select 
   COLA,
   rtrim (xmlagg (xmlelement (e, COLB || ' | ')).extract ('//text()'), ' | ') COLB
from 
   [table_name]
group by 
   COLA
;