Convert a column into a comma separated list

Database
Enthusiast

Convert a column into a comma separated list

I have a table that looks like this:

Column_A               Column_B

55555                     123

55555                     124

55555                     125

55555                     126

55555                     127

77777                     130

77777                     131

77777                     132

and I would like to convert the values in the second column into a comma separated list that should look like this:

Column_A                             Column_X

55555                               123,124,125,126,127

77777                               130,131,132

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.

Thanks

5 REPLIES
Junior Contributor

Re: Convert a column into a comma separated list

What's your Teradata release? Are XML services available?

SELECT * FROM dbc.FunctionsV
WHERE FunctionName = 'XMLAGG';

SELECT Column_A,  
TRIM(TRAILING ',' FROM (XMLAGG(Column_B || ','
ORDER BY ColumnB
) (VARCHAR(10000))))
FROM tab
GROUP BY 1
Enthusiast

Re: Convert a column into a comma separated list

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 15.1.0.0

No XML services are available.

Tried your code and got the following error: Data type “Column_B” does not match a defined type name.

Enthusiast

Re: Convert a column into a comma separated list

Try TDSTATS.UDFCONCAT introduced in 14.x

select Column_A,tdstats.udfconcat(Column_B)

from <<table-name>> 

group by Column_A;

Thanks!!

Enthusiast

Re: Convert a column into a comma separated list

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

Enthusiast

Re: Convert a column into a comma separated list

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

(select a.*,

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

     (

       select        t2.Column_A

       ,               t2.Column_B

       ,               1 as loop_counter

       ,               cast(t2.Column_B as varchar(300)) as Column_X

       from   test2 t2

       where  t2.SEQ_NUM = 1

       union all

       select t2.Column_A

       ,     t2.Column_B

       ,    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

from My_derived_table

qualify row_number() over(partition by Column_A order by loop_counter desc)=1

order by Column_A, loop_counter;