I have a table that looks like this:
and I would like to convert the values in the second column into a comma separated list that should look like this:
For each unique value in COLUMN_A I need to list all the values in COLUMN_B in a single row separated by a comma.
Any suggestions on how to do it in Teradata SQL will be appreciated.
What's your Teradata release? Are XML services available?
SELECT * FROM dbc.FunctionsV
WHERE FunctionName = 'XMLAGG';
TRIM(TRAILING ',' FROM (XMLAGG(Column_B || ','
ORDER BY ColumnB
GROUP BY 1
Thank you Dieter for responding to my query.
TD version: 15.00.0.05
Database version: Teradata 13.10.06.03
Provider version: Teradata.Net 220.127.116.11
No XML services are available.
Tried your code and got the following error: Data type “Column_B” does not match a defined type name.
Try TDSTATS.UDFCONCAT introduced in 14.x
group by Column_A;
Thank you VeluNatarajan for your suggestion.
I run your code and got the following error message: ‘Column tdstats not found in test1’
(test1 is the source table).
OK. I have found a solution.
/* Assign a row count to Column_B for each unique value of Column_A using ROW_NUMBER()*/
create multiset table test2 as
row_number() over(partition by Column_A order by Column_A) as SEQ_NUM
from test1 a) with data;
/* Use recursive processing to concatenate Column_B and store the product in Column_X*/
with recursive My_derived_table (Column_A, Column_B, loop_counter, Column_X) as
, 1 as loop_counter
, cast(t2.Column_B as varchar(300)) as Column_X
from test2 t2
where t2.SEQ_NUM = 1
, der.loop_counter + 1
, trim(der.Column_X) || ', ' || trim(t2.Column_B)
from test2 t2, My_derived_table der
where t2.Column_A = der.Column_A
and t2.SEQ_NUM = der.loop_counter + 1
and t2.SEQ_NUM > 1
select column_A, Column_X
qualify row_number() over(partition by Column_A order by loop_counter desc)=1
order by Column_A, loop_counter;