365 Days of Daily Coding: Day 130

I woke up quite late today unlike normal days where I wake up before 6.30am. I guess I was very tired last night. I was trying to use all my might to trying to figure out how to build a data viz in tableau. I have a vision in my mind on how I want the viz to look like however, its quite a tough one to achieve. More on the final outcome later.

Puzzle 15: Group Concatenation

I tried the solution in the live oracle environment here.

WITH temp1 AS (
SELECT 1 AS Seq, 'SELECT' AS Syntax FROM DUAL UNION ALL
SELECT 2 AS Seq, 'Product' AS Syntax FROM DUAL UNION ALL
SELECT 3 AS Seq, 'UnitPrice' AS Syntax FROM DUAL UNION ALL
SELECT 4 AS Seq, 'EffectiveDate' AS Syntax FROM DUAL UNION ALL
SELECT 5 AS Seq, 'FROM' AS Syntax FROM DUAL UNION ALL
SELECT 6 AS Seq, 'Products' AS Syntax FROM DUAL UNION ALL
SELECT 7 AS Seq, 'Where' AS Syntax FROM DUAL UNION ALL
SELECT 8 AS Seq, 'UnitPrice' AS Syntax FROM DUAL UNION ALL
SELECT 9 AS Seq, '>100' AS Syntax FROM DUAL 
), temp2 AS (
SELECT LISTAGG(Seq, '') WITHIN GROUP (ORDER BY Seq ASC) AS Seq, LISTAGG(Syntax, ' ') WITHIN GROUP (ORDER BY Seq ASC) "Syntax"
FROM temp1
WHERE Seq NOT IN (1,2,3,4)
), temp3 AS (
SELECT LISTAGG(Seq, '') WITHIN GROUP (ORDER BY Seq ASC) AS Seq, LISTAGG(Syntax, ', ') WITHIN GROUP (ORDER BY Seq ASC) "Syntax"
FROM temp1
WHERE Seq IN (2,3,4)
)
SELECT LISTAGG(Syntax, ' ') WITHIN GROUP (ORDER BY Seq ASC) AS Syntax
FROM (
SELECT TO_CHAR(Seq) AS Seq, Syntax FROM temp1
WHERE Seq = 1 UNION ALL
SELECT * FROM temp3
UNION ALL
SELECT * FROM temp2);