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?
