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:
x
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?
Advertisement
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))
)