with t1 as (select 'dfu1' dfu, 1 type, '01-Jan-2020' startdt, 10 period1, 20 period2, 30 period3, null period4, 50 period5 from dual
union all
select 'dfu1' dfu, 4 type, '01-Jan-2020' startdt, 1 period1, 2 period2, 3 period3, 4 period4, null period5 from dual
union all
select 'dfu1' dfu, 6 type, '01-Jan-2020' startdt, 1 period1, 1 period2, 1 period3, 1 period4, 1 period5 from dual
union all
select 'dfu2' dfu, 1 type, '01-Jan-2020' startdt, 40 period1, 50 period2, null period3, null period4, 50 period5 from dual
union all
select 'dfu2' dfu, 4 type, '02-Jan-2020' startdt, 1 period1, 2 period2, null period3, 4 period4, 5 period5 from dual
union all
select 'dfu2' dfu, 6 type, '03-Jan-2020' startdt, 1 period1, 1 period2, 1 period3, 1 period4, 1 period5 from dual)
select * from t1
DFU TYPE STARTDT PERIOD1 PERIOD2 PERIOD3 PERIOD4 PERIOD5
dfu1 1 01-Jan-2020 10 20 30 - 50
dfu1 4 01-Jan-2020 1 2 3 4 -
dfu1 6 01-Jan-2020 1 1 1 1 1
dfu2 1 01-Jan-2020 40 50 - - 50
dfu2 4 02-Jan-2020 1 2 - 4 5
dfu2 6 03-Jan-2020 1 1 1 1 1
If I take a dfu1 -> it has three records type1, type4, type6
for reach type record -> quantity for 4 periods, period1, period2, period3, period4
based on startdt column, for dfu1:type1:period1 indicates quantity for 01-Jan-2020,
for dfu1:type1:period2 indicates quantity for 01-Jan-2020 + 1 -> which is 02-Jan-2020,
for dfu1:type1:period3 indicates quantity for 01-Jan-2020 + 2 -> which is 03-Jan-2020,
for dfu1:type1:period4 indicates quantity for 01-Jan-2020 + 3 -> which is 04-Jan-2020
and so on... for dfu1:type4, dfu1:type6
now for a dfu1 -> I want to get a sum(qty) group by type,
This can be achieved easily as startdate for a dfu1 is same for all three types.
Whereas as for dfu2
type1, type4 and type6 have three different start dates.
so we cannot just sum (period1) to get period1 value.
for dfu2:type1 -> based on startdt column indicates quantity for 01-Jan-2020, for type 4 and type 6 there is no value for 01-Jan-2020, since type 4 and type 6 startdates and are starting from 02-Jan-2020 and 03-Jan-2020
so dfu2 sum of period 1 will be 40 only
for dfu2 -> period2 value is for 02-Jan-2020 and should calculated as SUM(dfu2:TYPE1:PERIOD2, dfu2:TYPE4:PERIOD1) there is no value for dfu2:type6 for 02-Jan-2020
for dfu2 -> period3 value should be SUM(TYPE1:PERIOD3,TYPE2:PERIOD2,TYPE3:PERIOD1)
for dfu2 -> period4 value should be SUM(TYPE1:PERIOD4,TYPE2:PERIOD3,TYPE3:PERIOD2)
for dfu2 -> period5 value should be SUM(TYPE1:PERIOD5,TYPE2:PERIOD4,TYPE3:PERIOD3)
and so on...
DFU STARTDT PERIOD1 PERIOD2 PERIOD3 PERIOD4 PERIOD5
dfu1 1-Jan-20 12 23 34 5 51
dfu2 1-Jan-20 40 51 3 1 55
Considerations,
startdate should min(startdate)
if there's no value for a period, should be considered as zero.
I was able to get the output by unpivot query, but looking if there's a possibility to achieve the output without unpivot as in my case, am unpivoting, doing the sum and pivoting again
I'm confused about what exactly is going on here. It feels the table mixes up row and column values... Showing us the pivot/unpivot solution you have would help!
Anyway, to get the output you asked for given the data shown, you could:
- Sum up the values by dfu & startdt
- Use lead to look forward to the next values for each period
Which gives the following rather convoluted query:
with t1 as (
select 'dfu1' dfu, 1 type, '01-Jan-2020' startdt, 10 period1, 20 period2, 30 period3, 0 period4, 50 period5 from dual
union all
select 'dfu1' dfu, 4 type, '01-Jan-2020' startdt, 1 period1, 2 period2, 3 period3, 4 period4, 0 period5 from dual
union all
select 'dfu1' dfu, 6 type, '01-Jan-2020' startdt, 1 period1, 1 period2, 1 period3, 1 period4, 1 period5 from dual
union all
select 'dfu2' dfu, 1 type, '01-Jan-2020' startdt, 40 period1, 50 period2, 0 period3, 0 period4, 50 period5 from dual
union all
select 'dfu2' dfu, 4 type, '02-Jan-2020' startdt, 1 period1, 2 period2, 0 period3, 4 period4, 5 period5 from dual
union all
select 'dfu2' dfu, 6 type, '03-Jan-2020' startdt, 1 period1, 1 period2, 1 period3, 1 period4, 1 period5 from dual
), rws as (
select dfu, startdt,
sum ( period1 ) period1,
sum ( period2 ) period2,
sum ( period3 ) period3,
sum ( period4 ) period4,
sum ( period5 ) period5
from t1
group by dfu, startdt
), tots as (
select dfu, startdt,
period1 p1,
period2 + lead (
period1, 1, 0
) over (
partition by dfu
order by startdt
) p2,
period3 + lead (
period2, 1, 0
) over (
partition by dfu
order by startdt
) + lead (
period1, 2, 0
) over (
partition by dfu
order by startdt
) p3,
period4 + lead (
period3, 1, 0
) over (
partition by dfu
order by startdt
) + lead (
period2, 2, 0
) over (
partition by dfu
order by startdt
) p4,
period5 + lead (
period4, 1, 0
) over (
partition by dfu
order by startdt
) + lead (
period3, 2, 0
) over (
partition by dfu
order by startdt
) p5,
min ( startdt ) over (
partition by dfu
) mn_dt
from rws
)
select * from tots
where startdt = mn_dt;
DFU STARTDT P1 P2 P3 P4 P5 MN_DT
dfu1 01-Jan-2020 12 23 34 5 51 01-Jan-2020
dfu2 01-Jan-2020 40 51 3 1 55 01-Jan-2020
I'm not convinced this solution meets the business requirement (whatever that is) though. And I'm sure it's more complex than the pivot/unpivot method you have.
Give us more background and we may be able to help better.