All Forums Database
philos188 4 posts Joined 11/13
20 May 2015
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
 
 
 
 
 

dnoeth 4628 posts Joined 11/04
20 May 2015

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

 

Dieter

philos188 4 posts Joined 11/13
21 May 2015

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.
 

TDThrottle 51 posts Joined 11/11
21 May 2015

Try TDSTATS.UDFCONCAT introduced in 14.x
select Column_A,tdstats.udfconcat(Column_B)
from <<table-name>> 
group by Column_A;
Thanks!!

philos188 4 posts Joined 11/13
21 May 2015

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

philos188 4 posts Joined 11/13
21 May 2015

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;
 

You must sign in to leave a comment.