Concatenating different values in a field...

UDA

Concatenating different values in a field...

I have 2 cols : Id,name

The data is present as:

Id Name
1 abc
1 def
1 ghi
2 xy
2 zv

I want to display the data as :
1 abc,def,ghi
2 xy,zv

Can this be achieved thru a single sql query?I dont want to use any loops or anything.
Basically my aim is to concatenate all values of a single id and display it in a single row.
Thanks in advance!
2 REPLIES

Re: Concatenating different values in a field...

You will have to cook up a recursive query, something like this ....

WITH RECURSIVE RECTABLE(ID_, NAME_, PREV_NAME_)
AS
(
SELECT ID, MIN(NAME) (VARCHAR(1000)) , MIN(NAME)
FROM MYDATA
GROUP BY 1

UNION ALL

SELECT ID, NAME_ || ',' || NAME, NAME
FROM MYDATA INNER JOIN RECTABLE
ON ID = ID_
AND NAME > PREV_NAME_

)

SELECT ID_, NAME_
FROM RECTABLE
QUALIFY ROW_NUMBER() OVER(PARTITION BY ID_ ORDER BY CHARACTER_LENGTH(NAME_) DESC) = 1
;

Re: Concatenating different values in a field...

Thank you! it works perfectly..