All Forums General
rekha.vinu86 6 posts Joined 11/14
11 Nov 2014
Convert multiple rows into single row

How to convert multiple rows into single row in Teradata using concatenation and based on sort order of a column.
 
For eg: we have data like this.
 
Itinerary PromoCode  PromoType
1                 ABC                     200
1                 DEF                     100
1                 GHI                     300
2                 JKL                       100
2                 MNO                  200
 
Result should be as below.Promocode and PromoType values of same itinerary should be concatenated and this concatenation should happen based on PromoType ascending order.
 
Itinerary  PromoCode     PromoType
1                 DEF:ABC:GHI   100:200:300
2                 JKL:MNO          100:200

Raja_KT 1246 posts Joined 07/09
11 Nov 2014

which version of TD?
You can use : select Itinerary , tdstats.udfconcat(trim(PromoCode )) from yourtable group by 1
 
or do you want specifically : delimiter too?

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
11 Nov 2014

There's no way to determine a specific order for UDFConcat, you need to use the old style max(case)

SELECT Itinerary,
   MAX(CASE WHEN rn = 1 THEN        TRIM(PromoType) ELSE '' end) ||
   MAX(CASE WHEN rn = 2 THEN ':' || TRIM(PromoType) ELSE '' end) ||
   MAX(CASE WHEN rn = 3 THEN ':' || TRIM(PromoType) ELSE '' end) ||
   ... 


   MAX(CASE WHEN rn = 1 THEN        TRIM(PromoCode) ELSE '' end) ||
   MAX(CASE WHEN rn = 2 THEN ':' || TRIM(PromoCode) ELSE '' end) ||
   MAX(CASE WHEN rn = 3 THEN ':' || TRIM(PromoCode) ELSE '' end) ||
   ...
FROM
 (
   SELECT Itinerary, PromoCode, PromoType, 
      ROW_NUMBER() OVER (PARTITION BY Itinerary ORDER BY PromoType) AS rn
   FROM dropme
 ) AS t
GROUP BY 1

Of course you need to know the maximum number of rows per Itinerary.

Dieter

Raja_KT 1246 posts Joined 07/09
11 Nov 2014

Yes Dieter,indeed, the sorting  based on PromoType, back to the old style.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.