UNPIVOT in Snowflake and adding additional columns

I am trying to unpivot a table TABLE_A and add additional columns to the query

TABLE_A:

YEAR	ID	OCT	NOV	DEC	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP
2022	1	10	5	5	10	5	10	15	20	5	0	0	10

Expected output:

ID	MONTHLY_NO	MONTH	START_DATE	YEAR
1	10	10	10-01-2021	2021
1	5	11	11-01-2021	2021
1	5	12	12-01-2021	2021
1	10	1	01-01-2022	2022
1	5	2	02-01-2022	2022
1	10	3	03-01-2022	2022
1	15	4	04-01-2022	2022
1	20	5	05-01-2022	2022
1	5	6	06-01-2022	2022
1	0	7	07-01-2022	2022
1	0	8	08-01-2022	2022
1	10	9	09-01-2022	2022

Query that I used:

SELECT ID, 
MONTHLY_NO, 
CASE 
WHEN TGT_MONTH='JAN' THEN 1
WHEN TGT_MONTH='FEB' THEN 2
WHEN TGT_MONTH='MAR' THEN 3
WHEN TGT_MONTH='APR' THEN 4
WHEN TGT_MONTH='MAY' THEN 5
WHEN TGT_MONTH='JUN' THEN 6
WHEN TGT_MONTH='JUL' THEN 7
WHEN TGT_MONTH='AUG' THEN 8
WHEN TGT_MONTH='SEP' THEN 9
WHEN TGT_MONTH='OCT' THEN 10
WHEN TGT_MONTH='NOV' THEN 11
WHEN TGT_MONTH='DEC' THEN 12
END AS MONTH
FROM TABLE_A
UNPIVOT( MONTHLY_NO FOR TGT_MONTH IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC));

How can I add START_DATE and YEAR columns to the query? Could you please advise?

Answer

Following the pattern in the UNPIVOT doc’s, with your table_a

SELECT *
FROM table_a
UNPIVOT( monthly_no FOR tgt_month IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec))

should give (ignoring lack of ordering, and assuming year, id, and monthly_no are both NUMBER’s):

YEAR    ID  TGT_MONTH   MONTHLY_NO
2022    1   'JAN'       10
2022    1   'FEB'       5
2022    1   'MAR'       10
2022    1   'APR'       15
2022    1   'MAY'       20
2022    1   'JUN'       5
2022    1   'JUL'       0
2022    1   'AUG'       0
2022    1   'AUG'       10
2022    1   'OCT'       10
2022    1   'NOV'       5
2022    1   'DEC'       5

if you are not bothered by column order to can use a single inline form:

SELECT year, 
    id, 
    monthly_no, 
    TO_DATE(year::text || tgt_month, 'YYYYMON') AS start_date, 
    MONTH(start_date) AS month
FROM table_a
UNPIVOT( monthly_no FOR tgt_month IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec))

otherwise to get it in the order you want you have to do the same logic repeatedly, or nest it.

repaeted version:

SELECT 
    id, 
    monthly_no, 
    MONTH(TO_DATE(year::text || tgt_month, 'YYYYMON')) AS month,
    TO_DATE(year::text || tgt_month, 'YYYYMON') AS start_date,
    year    
FROM table_a
UNPIVOT( monthly_no FOR tgt_month IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec))

nested:

SELECT 
    id, 
    monthly_no, 
    MONTH(start_date) AS month,
    start_date,
    year    
FROM (
    SELECT 
        id, 
        monthly_no, 
        TO_DATE(year::text || tgt_month, 'YYYYMON') AS start_date,
        year    
    FROM table_a
    UNPIVOT( monthly_no FOR tgt_month IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec))
)

Oh, I have just grok’ed you Oct, Nov, Dec are 2021 on purpose, even though you don’t mention it.. This can be fixed with some inline IFF

SELECT 
    id, 
    monthly_no, 
    MONTH(start_date) AS month,
    IFF(month >= 10, DATEADD('year', -1, start_date), start_date) AS start_date
    IFF(month >= 10, year -1, year) AS year 
FROM (
    SELECT 
        id, 
        monthly_no, 
        TO_DATE(year::text || tgt_month, 'YYYYMON') AS start_date,
        year    
    FROM table_a
    UNPIVOT( monthly_no FOR tgt_month IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec))
)