Not something that comes up often, but can be a hassle to workout the logic behind merging multiple rows into a single field. If your not sure what a vertical concatonation is here is an example –
Table
Item# Description
1 “This ”
2 “is ”
3 “how ”
4 “it ”
5 “works ”
Result
“This is how it works”
I have used vertical concat’s for a variety of scripts but normaly handy where you have an Account tables with multiple products for each account and you want to display all the product together. The below example was design specifically for that example.
/** Vertical concat to group products **/
CREATE VOLATILE TABLE Group_prod AS (
SELECT sq1.account_no
,MAX(CASE WHEN sq1.flag = 1 THEN product ELSE ” END ) ||
MAX(CASE WHEN sq1.flag = 2 THEN ‘, ‘ || product ELSE ” END ) ||
MAX(CASE WHEN sq1.flag = 3 THEN ‘, ‘ || product ELSE ” END ) ||
MAX(CASE WHEN sq1.flag = 4 THEN ‘ ,’ || product ELSE ” END ) ||
MAX(CASE WHEN sq1.flag = 5 THEN ‘, ‘ || product ELSE ” END ) AS ConcatenatedRows
FROM
(
SELECT account_no
,product
,RANK() OVER (PARTITION BY account_no
ORDER BY product DESC) AS “Flag”
FROM acct_info) sq1
GROUP BY 1
)WITH DATA ON COMMIT PRESERVE ROWS;
This example is specifically for Teradata, but can be adapted to oracle and mysql(rank need to be changed). For the above example I didn’t need to worry about order so much so it has just been ORDER BY product, but depending on your needs you would just apply your filter in the ORDER BY of the RANK.