Convert Multiple Rows into Single Row in SQL in Teradata

Recently Krish asked on our Facebook page about how to convert multiple rows into one row using sql. Also, Raghav asked via contact form how to get all column list of a table in one single column into a volatile table. So we thought of proposing a single solution to both the problems. We will use recursive query to convert multiple rows into one single row where each row value will be separated by a delimiter ( in this case, it is ~). So recursive query needs a base query and then with that base query it joins other rows on the basis of some conditions. Now lets see the query below:

Input:

select columnname FROM dbc.COLUMNS
WHERE tablename='TableSizeV'
AND databasename='dbc '
order by columnid;

Let's convert 6 rows 1 column into 1 column 1 row with all the 6 values concatenated into one single value separated by "~"

Output:

WITH RECURSIVE str_columns(columnid,columnname)
AS
(
SEL columnid,CAST(columnname AS VARCHAR(200)) AS columnname
FROM dbc.COLUMNS
WHERE tablename='TableSizeV'
AND databasename='dbc'
QUALIFY ROW_NUMBER() OVER(ORDER BY columnid ASC)=1
UNION ALL
SEL tb1.columnid,TRIM(tb2.columnname)||'~'||tb1.columnname
FROM dbc.COLUMNS tb1 , str_columns tb2
WHERE tb1.columnid=tb2.columnid+1
AND tb1.tablename='TableSizeV'
AND tb1.databasename='dbc'
)
SEL columnname
FROM (
SEL TOP 1 columnid,columnname
FROM str_columns
ORDER BY columnid DESC
)tb1;

This is one way of converting multiple rows into single row using recursive query. However now in Teradata new versions, there are in-built functions like xmlagg & tdstats.udfconcat which you can use to replicate same without using recursive queries.

Check this post to use functions in place of Recursive queries.