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:
Let's convert 6 rows 1 column into 1 column 1 row with all the 6 values concatenated into one single value separated by "~"
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.