I’m trying to return calendar hierarchy from my table. Year -> Quarter – > Month
Since, I don’t have Id & parent Id mapping in my table, I’m using ROW_NUMBER() concept to map relative data and using UNION listing hierarchical data with different columns.
Here’s my code:
SELECT ROW_NUMBER() OVER (ORDER BY parent_cal_id, cal_year, cal_quarter, cal_month) cal_id, cal_data, cal_month, cal_quarter, cal_year, parent_cal_id from ( select * from ( select FISCAL_YEAR cal_data, NULL cal_month, NULL cal_quarter, NULL cal_year, 0 parent_cal_id from MFP_MASTER.FISCAL_CALENDAR where WEEK_BEGIN_DATE > to_date('01012019', 'DDMMYYYY') and WEEK_END_DATE < to_date('31122019', 'DDMMYYYY') order by FISCAL_YEAR ) union select * from ( select FISCAL_QUARTER cal_data, NULL cal_month, FISCAL_QUARTER, FISCAL_YEAR, 1 from MFP_MASTER.FISCAL_CALENDAR where WEEK_BEGIN_DATE > to_date('01012019', 'DDMMYYYY') and WEEK_END_DATE < to_date('31122019', 'DDMMYYYY') order by FISCAL_YEAR, FISCAL_QUARTER ) union select * from ( select FISCAL_MONTH cal_data, FISCAL_MONTH cal_month, FISCAL_QUARTER, FISCAL_YEAR, 2 from MFP_MASTER.FISCAL_CALENDAR where WEEK_BEGIN_DATE > to_date('01012019', 'DDMMYYYY') and WEEK_END_DATE < to_date('31122019', 'DDMMYYYY') order by FISCAL_YEAR, FISCAL_QUARTER, FISCAL_MONTH ) ) order by 1 ;
But I wanted data as below,
ID | DATA | PARENT_ID -------+----------+---------------- 1 | 2019 | 0 2 | Q1 | 1 3 | Q2 | 1 4 | Q3 | 1 5 | Q4 | 1 6 | Jan | 2 7 | Feb | 2 8 | Mar | 2 9 | Apr | 3 10 | May | 3 11 | Jun | 3
How can I achieve this using my Query? Please help on this.
Advertisement
Answer
Depending on the column week_date_date
you can construct such an unpivoting query :
with t as ( select distinct data, col as parent_id from ( select to_char(week_date_date,'yyyy') as cal_year, concat('Q',to_char(week_date_date,'Q')) as cal_quarter, to_char(week_date_date,'Mon') as cal_month from fiscal_calendar ) unpivot (data for col in (cal_year as '0', cal_quarter as '1', cal_month as '2') ) ) select row_number() over (order by parent_id, case when parent_id = 1 then data end, case when parent_id = 2 then to_date(data,'mm') end) as id, t.* from t; ID DATA PARENT_ID -- ---- --------- 1 2019 0 2 Q1 1 3 Q2 1 4 Q3 1 5 Q4 1 6 Jan 2 7 Feb 2 8 Mar 2 9 Apr 2 10 May 2 11 Jun 2 12 Jul 2 13 Aug 2 14 Sep 2 15 Oct 2 16 Nov 2 17 Dec 2