how to merge multiple rows in a single row by oracle sql

Question

my data be like :

ROW    DATA
1       OK
1       OK
2       ERROR
2       ERROR

I want merge data group by row

ROW     DATA
1       OK,OK
2       ERROR,ERROR

WHICH function could work out ?

Hana · Accepted Answer · 2022-05-20 04:04:02Z

1

In Oracle, you can use LISTAGG to query the same:

select ROW_, 
       listagg(DATA_, ',') within group (order by ROW_) as DATAS 
from MYTBL
group by ROW_

Test query in fiddle

answered May 20, 2022 at 4:04

Hana

8344 gold badges15 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

d r · Accepted Answer · 2022-05-20 05:31:02Z

0

For oracle versions older then 11g Release 2 you can use WMSYS package, function WM_CONCAT()

Select ROW_, 
       WMSYS.WM_CONCAT(DATA_)  
From MYTBL
Group By ROW_

answered May 20, 2022 at 5:31

d r

8,6622 gold badges9 silver badges19 bronze badges

[Code: 904, SQL State: 42000] ORA-00904: "WM_CONCAT": invalid identifier