TD Newbie: help needed to Convert rows to a column

Database

TD Newbie: help needed to Convert rows to a column

Hi All,

I'm new to TD and need help converting rows into a single column; 

Sample input data.

ID   key2 Indicator  

123 ABC    A1

123 ABC    B1

123 ABC    C1

345 CDE    A1

345 CDE    B1

345 CDE    C1

567 XYZ     B1

567 XYZ     C1

Output requested: 

ID   key2  concatenated-field   

123 ABC    A1B1C1

345 CDE    A1B1C1

567 XYZ     B1C1

Also, can we change the values in the concatenated field based on the inputs I.e A1 to X1 etc


Please help me.

Thank you in advance...

Govin 

4 REPLIES

Re: TD Newbie: help needed to Convert rows to a column

Hello TD Gurus - any help you can provide to resolve my query above would be greatly appreciated...please help..!

Re: TD Newbie: help needed to Convert rows to a column

Re: TD Newbie: help needed to Convert rows to a column

Thank you Saeed. I shall try these and will update you with my findings..!!

Re: TD Newbie: help needed to Convert rows to a column

WITH RECURSIVE MYREC(id,key2,indicator,LVL)

 AS

 (

 SELECT id, key2,MIN(indicator(VARCHAR(1000))) AS indicator,1

 FROM <table>

GROUP BY 1

 UNION ALL

--RECURSIVE Join

 SELECT b.id,b.key2,trim(a.indicator) || ',' || trim( b.indicator), LVL+1

 FROM <table> a INNER JOIN MYREC b

 ON a.id = b.id

 AND a.indicator > b.indicator

 )

 SELECT id,key2,indicator,lvl

 FROM MYREC

 QUALIFY RANK() OVER(PARTITION BY id,key2,ORDER BY indicator DESC) = 1