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;
ColumnName |
---|
Vproc |
DataBaseName |
AccountName |
TableName |
CurrentPerm |
PeakPerm |
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;
columnname |
---|
Vproc~DataBaseName~AccountName~TableName~CurrentPerm~PeakPerm |
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.