Transposed multiple rows into one column and one row

Database

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.

7 REPLIES
WAQ
N/A

Re: Transposed multiple rows into one column and one row

Try using recursive SQL.

Re: Transposed multiple rows into one column and one row

Yup, found the solution using recursive. Thanks.

WAQ
N/A

Re: Transposed multiple rows into one column and one row

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

Teradata Employee

Re: Transposed multiple rows into one column and one row

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

Re: Transposed multiple rows into one column and one row

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..

Re: Transposed multiple rows into one column and one row

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
;

Re: Transposed multiple rows into one column and one row

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
;