How to concatenate strings in different rows of a recordset, using a common id?
Example
I have a table (test123) like this
IdValue DataValue
———————
1 aaaaa
2 bbbbb
1 ccccc
2 ddddd
Desired output
IdValue Data_Concatenated
———————-
1 aaaaa,ccccc
2 bbbbb,ddddd
Solution 1: Using sys_connect_by_path
SELECT idvalue ,
SUBSTR(MAX(sys_connect_by_path(datavalue, ‘,’ )),2) data_concatenated
FROM
(SELECT idvalue ,
datavalue ,
row_number() over (partition BY idvalue order by datavalue) row#
FROM test123
)
START WITH row# = 1
CONNECT BY prior row# = row#-1
AND prior idvalue = idvalue
GROUP BY idvalue
ORDER BY idvalue;
Solution 2:
SELECT idvalue, RTRIM (EXTRACT (XMLAGG (XMLELEMENT (“X”, dataVALUE || ‘,’)), ‘/X/text()’),’,’) VALUE
FROM test123
group by IDvalue;