Convert multiple rows into single row

General
Enthusiast

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

3 REPLIES
Enthusiast

Re: Convert multiple rows into single row

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?

Junior Contributor

Re: Convert multiple rows into single row

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.

Enthusiast

Re: Convert multiple rows into single row

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