zmila zmila - 3 months ago 8
SQL Question

sql query: pivot but with different count or rows

What is the best variant to construct query to collect rows into columns like pivot, but without aggregation and several rows may be in each column?

Sample data:

id, parent_fk, month, quantity
10, 1111, 'jan', 21
11, 1111, 'jan', 24
12, 1111, 'feb', 12

13, 2222, 'jan', 3
14, 2222, 'feb', 4
15, 2222, 'mar', 5

16, 3333, 'feb', 77
17, 3333, 'feb', 88
16, 3333, 'mar', 99


Each month may contain different number of rows. I have to put them alongside and justify by heigths.
I'd like to show these data as:

parent_fk, jan, feb, mar
1111, 21, 12, __
1111, 24, __, __
2222, 3, 4, 5
3333, __, 77, 99
3333, __, 88, __


Currently I have such not-elegant query (create ordering inside each month with row_number over partition, and join all the monthes by this ordering):

select
nvl(JAN.parent_fk, nvl(FEB.parent_fk, nvl(MAR.parent_fk, -1))) calc_parent_fk,
JAN.quantity jan,
FEB.quantity feb,
MAR.quantity mar
from
(select id, parent_fk, quantity,
row_number() OVER (PARTITION BY parent_fk, mnth order by id) "RN"
from T_MONTH_DATA
where mnth = 'jan') JAN
full join
(select id, parent_fk, quantity,
row_number() OVER (PARTITION BY parent_fk, mnth order by id) "RN"
from T_MONTH_DATA
where mnth = 'feb') FEB
on JAN.parent_fk = FEB.parent_fk and JAN.RN = FEB.RN
full join
(select id, parent_fk, quantity,
row_number() OVER (PARTITION BY parent_fk, mnth order by id) "RN"
from T_MONTH_DATA
where mnth = 'mar') MAR
on FEB.parent_fk = MAR.parent_fk and FEB.RN = MAR.RN
order by 1


Is there more simple way to present such data?
How to get resulting parent_fk? (I use nested NVL, because I don't know which of the monthes contain max of rows, and all other monthes have null there).

Answer

In Oracle 11g use pivot clause and function row_number() like this:

select parent_fk, jan, feb, mar 
  from ( 
    select row_number() over (partition by mnth, parent_fk order by id) rn, 
           parent_fk, mnth, quantity 
      from t_month_data )
  pivot (sum(quantity) for mnth in ('jan' jan, 'feb' feb, 'mar' mar))
  order by parent_fk

Test data and output:

create table t_month_data (id number(4), parent_fk number(4), 
                           mnth varchar2(3), quantity number(6));

insert into t_month_data values (10, 1111, 'jan', 21);
insert into t_month_data values (11, 1111, 'jan', 24);
insert into t_month_data values (12, 1111, 'feb', 12);
insert into t_month_data values (13, 2222, 'jan',  3);
insert into t_month_data values (14, 2222, 'feb',  4);
insert into t_month_data values (15, 2222, 'mar',  5);
insert into t_month_data values (16, 3333, 'feb', 77);
insert into t_month_data values (17, 3333, 'feb', 88);
insert into t_month_data values (16, 3333, 'mar', 99);

PARENT_FK        JAN        FEB        MAR
--------- ---------- ---------- ----------
     1111         21         12 
     1111         24            
     2222          3          4          5
     3333                    77         99
     3333                    88