teradata

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:

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

Output:

Multiple Rows into Single Row
Multiple Rows into Single Row

Leave a Reply

Your email address will not be published. Required fields are marked *