Skip to content
Advertisement

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:

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

Advertisement

Answer

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

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

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

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

repaeted version:

nested:

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

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement