Hierarchy data from Oracle from Multiple Tables

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.

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

Demo