All Forums UDA
adash-7422 33 posts Joined 01/09
18 May 2009
Concatenating different values in a field...

I have 2 cols : Id,nameThe data is present as:Id Name1 abc1 def1 ghi2 xy2 zvI want to display the data as :1 abc,def,ghi2 xy,zvCan 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!

joedsilva 505 posts Joined 07/05
18 May 2009

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 MYDATAGROUP BY 1UNION ALLSELECT ID, NAME_ || ',' || NAME, NAMEFROM MYDATA INNER JOIN RECTABLEON ID = ID_AND NAME > PREV_NAME_)SELECT ID_, NAME_FROM RECTABLEQUALIFY ROW_NUMBER() OVER(PARTITION BY ID_ ORDER BY CHARACTER_LENGTH(NAME_) DESC) = 1;

adash-7422 33 posts Joined 01/09
19 May 2009

Thank you! it works perfectly..

You must sign in to leave a comment.