All Forums Database
KS42982 137 posts Joined 12/12
19 Dec 2012
Transposed multiple rows into one column and one row

Hi, 
I have 2 tables. One table with 1 column and multiple rows (say, 5) and another table is also with 1 column but can have only 1 row. I need to store all the rows of table 1 in the one column/row of table 2. All the table 1 values should be seperated by comma. 
Below is example to give more clarity. 
Table 1
Column_1 (consider it as source table)
a
b
c
d
e
 
Table 2 (consider it as target table)
Column_1
a,b,c,d,e
How can I achieve that using Teradata ? Please keep in mind the number of rows in Table 1 is unknown. Basically, I need to create some generic code which can achieve above kind of scenario where I can pass any table name with 1 column and would need to have transposed comma seperated output in table 2.  
Please help me out. 
Thanks.

WAQ 158 posts Joined 02/10
19 Dec 2012

Try using recursive SQL.

KS42982 137 posts Joined 12/12
20 Dec 2012

Yup, found the solution using recursive. Thanks.

WAQ 158 posts Joined 02/10
21 Dec 2012

It's always nice to share your solution (like SQL) so that other can get benfit from the same.

ToddAWalter 316 posts Joined 10/11
21 Dec 2012

This is also an application for an aggregate UDF that concatenates each sucessive sucessive value that it is presented, returning the concatenated string.

mohan.mscss 31 posts Joined 04/11
21 Dec 2012

I hope TD can support upto 64000 characters to store in a column. in such a case if we implement recursive process then there should be rework in future since we do not know the exact no of rows as well as we are adding comma between two rows....
 
And I would like to know how we can skip to another row based on current row value in the recursive process. can any one help me here..
 
 

 

KS42982 137 posts Joined 12/12
26 Dec 2012

This is how I resolved my issue, that might help.

WITH RECURSIVE base (c_rnk, c_list)
AS
(
SELECT 
rnk c_rnk, 
CAST(TRIM(columnname) AS VARCHAR(5000)) c_list
FROM MYDB.MYTB
WHERE rnk = 1

UNION ALL

SELECT 
rnk c_rnk,
b.c_list || ',' || CAST(TRIM(c.columnname) AS VARCHAR(5000)) c_list
FROM MYDB.MYTB c
INNER JOIN base b
ON b.c_rnk + 1 = c.rnk

)

SELECT  c_list FROM base
QUALIFY RANK() OVER (ORDER BY c_rnk DESC) = 1
;
KS42982 137 posts Joined 12/12
26 Dec 2012

One thing I forgot to mention above.
In my query, field called 'rnk' of MYDB.MYTB is the ranked column by columname values. It happens before the recursive query starts. Like below -

INSERT INTO MYDB.MYTB
SELECT columnname
,RANK () OVER (ORDER BY columnid) rnk
FROM MYDB2.MYTB2
;

 
 
 

You must sign in to leave a comment.