Transpose and Concatenating Values

Database
Enthusiast

Transpose and Concatenating Values

Hi,

I have a requirement as below:-

the table structure is as below:-

ID - VALUE

id1 - val1

id2 - val2

id3 - val3

id3 - val4

id4 - val5

id4 - val6

id4 - val7

id4 - val8

requirement is to concatenate all values and put in one column for each ID with a limiatation of considering only 2 values.

Hence, I want to generate output as below:-

ID - NEW_VALUE

id1 - val1

id2 - val2

id3 - val3; val4

Here id4 is not considered for the output as it has 4 values.

Can anyone advise how this can be achieved in Teradata using queries.

Thanks a lot in Advance.

Sagar

3 REPLIES
Senior Apprentice

Re: Transpose and Concatenating Values

Hi Sagar,

only two values?

Then it's easy:

SELECT
ID,
MIN(VALUE) ||
CASE
WHEN COUNT(*) = 1 THEN ''
ELSE ';' || MAX(value)
END AS NEW_VALUE
FROM tab
GROUP BY 1
HAVING COUNT(*) <= 2
Enthusiast

Re: Transpose and Concatenating Values

Hi Dieter,

Thanks for the query and it is working to concatenate only MIN and MAX values.

I think I need to provide more details about the requirement:-

ID - VALUE

id1 - val11

id2 - val22

id2 - val33

id3 - val33

id3 - val54

id3 - val67

id4 - val13

id4 - val14

id4 - val18

id4 - val90

id5 - val13

id5 - val14

id5 - val18

id5 - val90

id5 - val91

Actual requirement is for 10 values but, to minimize requested logic for 2 values.

Could you please advise to extract 4 or less than 4 values by concatenating all the values.

The expected output should be as below:-

ID - NEW_VALUE

id1 - val11

id2 - val22; val33

id3 - val33; val54; val67

id4 - val13; val14; val18; val90

Thanks a lot in advance.

Sagar

Enthusiast

Re: Transpose and Concatenating Values

You need to write the recursive query.

I haven't tested this SQL, but I hope it will work for you

WITH RECURSIVE LOOKUP(ID,VALUE,LVL)
AS
(
SELECT ID, MIN(VALUE(VARCHAR(1000))) AS VALUE,1 as LVL
FROM table1
GROUP BY 1

UNION ALL

SELECT b.ID,trim(a.VALUE) || ';' || trim( b.VALUE), LVL+1
FROM table1 a INNER JOIN LOOKUP b
ON a.ID = b.ID
AND a.VALUE > b.VALUE
)

SELECT ID, VALUE, LVL
FROM LOOKUP
QUALIFY RANK() OVER(PARTITION BY id ORDER BY VALUE DESC) = 1;