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.
Column_1 (consider it as source table)
Table 2 (consider it as target table)
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.
This is also an application for an aggregate UDF that concatenates each sucessive sucessive value that it is presented, returning the concatenated string.
This is how I resolved my issue, that might help.
WITH RECURSIVE base (c_rnk, c_list)
CAST(TRIM(columnname) AS VARCHAR(5000)) c_list
WHERE rnk = 1
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
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
,RANK () OVER (ORDER BY columnid) rnk