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

TABLE_A:

Expected output:

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?

CodePudding user response：

You should be able just to select the additional columns you need. I used a SQL server example for this, but in principle it should work the same way. See my query below:

SELECT ID, 
year,
CASE
WHEN TGT_MONTH='JAN' THEN '01/01/' CAST(year as CHAR(4))
WHEN TGT_MONTH='FEB' THEN '02/01/' CAST(year as CHAR(4))
WHEN TGT_MONTH='MAR' THEN '03/01/' CAST(year as CHAR(4))
WHEN TGT_MONTH='APR' THEN '04/01/' CAST(year as CHAR(4))
WHEN TGT_MONTH='MAY' THEN '05/01/' CAST(year as CHAR(4))
WHEN TGT_MONTH='JUN' THEN '06/01/' CAST(year as CHAR(4))
WHEN TGT_MONTH='JUL' THEN '07/01/' CAST(year as CHAR(4))
WHEN TGT_MONTH='AUG' THEN '08/01/' CAST(year as CHAR(4))
WHEN TGT_MONTH='SEP' THEN '09/01/' CAST(year as CHAR(4))
WHEN TGT_MONTH='OCT' THEN '10/01/' CAST(year as CHAR(4))
WHEN TGT_MONTH='NOV' THEN '11/01/' CAST(year as CHAR(4))
WHEN TGT_MONTH='DEC' THEN '12/01/' CAST(year as CHAR(4))
END as starting_date,
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 
  (
     SELECT year, id, oct, nov, dec, jan, feb, mar, apr, may, jun, jul, aug, sep
     FROM monthly_sales
  ) as pvt
  UNPIVOT(MONTHLY_NO FOR TGT_MONTH IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC))
  As unpvt;

You can see my sqlfiddle here: http://sqlfiddle.com/#!18/48ee0/7/0

CodePudding user response：

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))
)