SQL Concatenation of multiple rows

Question

I use Teradata, I have a table like this

ID String 123 Jim 123 John 123 Jane 321 Jill 321 Janine 321 Johan 

I want to query a table to get

 ID String 123 Jim, John, Jane 321 Jill, Janine, Johan 

I tried the section, but there can be many names. How to get this result. Even to point me in the right direction would be great.

Answer 1

Unfortunately, there is no PIVOT in Teradata (only TD_UNPIVOT at 14.10).

If you're lucky, there is a consolidated UDF on your site to make a concat group (perhaps a small opportunity).

Otherwise, there are two options: recursion or aggregation.

If the maximum number of rows per identifier is known, then aggregation is usually faster. This is a lot of code, but most of them are based on cut and paste.

 SELECT id, MAX(CASE WHEN rn = 1 THEN string END) || MAX(CASE WHEN rn = 2 THEN ',' || string ELSE '' END) || MAX(CASE WHEN rn = 3 THEN ',' || string ELSE '' END) || MAX(CASE WHEN rn = 4 THEN ',' || string ELSE '' END) || ... -- repeat up to the known maximum FROM ( SELECT id, string, ROW_NUMBER() OVER (PARTITION BY id ORDER BY string) AS rn FROM t ) AS dt GROUP BY 1; 

For large tables, this is much more efficient when you materialize the result of a view in a flying table first, using the GROUP BY column as PI.

For recursion, you should also use the Volatile table, since OLAP functions are not allowed in the recursive part. Using a view instead will compute the OLAP function multiple times and therefore lead to poor performance.

 CREATE VOLATILE TABLE vt AS ( SELECT id ,string ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY string DESC) AS rn -- reverse order! ,COUNT(*) OVER (PARTITION BY id) AS cnt FROM t ) WITH DATA UNIQUE PRIMARY INDEX(id, rn) ON COMMIT PRESERVE ROWS; WITH RECURSIVE cte (id, list, rn) AS ( SELECT id ,CAST(string AS VARCHAR(1000)) -- define maximum size based on maximum number of rows ,rn FROM vt WHERE rn = cnt UNION ALL SELECT vt.id ,cte.list || ',' || vt.string ,vt.rn FROM vt JOIN cte ON vt.id = cte.id AND vt.rn = cte.rn - 1 ) SELECT id, list FROM cte WHERE rn = 1; 

There is one problem with this approach, it may take a lot of coils, which is easy to see when you omit WHERE rn = 1 .