How to get output displaying as rows to columns
I have the code which produces the output format below (Output A).
Looking for suggestions on how to modify the SQL below to produce the desired output B ..
Note: The columns INTV1 = First interval_number , INTV2 = 2nd interval number.. et al. The values below INTV1 is the interval_value column for 1st interval.
Output A:
Desired Output B:
Looking for suggestions on how to modify the SQL below to produce the desired output B ..
Note: The columns INTV1 = First interval_number , INTV2 = 2nd interval number.. et al. The values below INTV1 is the interval_value column for 1st interval.
WITH opdays as
( --
select opday as opdaystart, -- opday is derived from the inner Select query
(opday + 1) - (1 / 86400) as opdaystop, -- opdaystop is derived by adding a day and subtract a second from that day
trunc(to_date('10/1/2014 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) as FirstDayStarttime, -- gv_firstdaystarttime = TRUNC(p_firstday) where firstday will be parameter passed in from appworx
trunc(to_date('10/1/2014 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) as FirstDayStoptime, -- gv_firstDaystoptime = TRUNC(p_firstday) + 1 - (1 / 86400), add a day and subtract a second from firstday
trunc(to_date('12/31/2014 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) as LastDayStarttime, -- gv_lastdaystarttime = TRUNC(p_lastday) where lastday will be parameter passed in from appworx
trunc(to_date('12/31/2014 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) as LastDayStoptime -- gv_lastdaystoptime = TRUNC(p_lastday) + 1 - (1 / 86400), add a day and subtract a second from lastday
from (--
select trunc(to_date('10/1/2014 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + (LEVEL - 1) as opday -- derive operating day where gv_firstdaystarttime = TRUNC(p_firstday) passed in by appworx
from dual
connect by level <= 1000 -- Get 1000 dateoffset
--
) Z
where Z.opday <= trunc(to_date('12/31/2014 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) -- as long as operating day is within the last day of quarter
--
)
SELECT
S.opdaystart as tradedate,
'DAY' AS INTERVALDATASOURCE,
b.billdetermcode,
S.FirstDayStarttime,
S.FirstDayStoptime,
S.LastDayStarttime,
S.LastDayStoptime,
i.interval_number,
i.interval_value
FROM rpv_parsed_daioutput i
JOIN billdeterminant b ON (i.uidbilldeterminant = b.uidbilldeterminant
and b.billdetermcode = 'COMPETITIVE') -- to filter out by COMPETITIVE billdeterminant
JOIN (--
SELECT O.opdaystart,
O.opdaystop,
l.billdetermcode,
O.FirstDayStarttime,
O.FirstDayStoptime,
O.LastdayStarttime,
O.LastdayStoptime,
MAX(hx.savechannel) as maxsavechannel
FROM daioutputinterval ix
JOIN daioutputheader hx ON (ix.uiddaioutputheader = hx.uiddaioutputheader) -- join on header table to get daioutputheader interval data
JOIN billdeterminant l ON (hx.uidbilldeterminant = l.uidbilldeterminant ) -- join on billdeterminant
JOIN opdays O -- Join for all operating days in the Period
ON (
--
(ix.starttime BETWEEN trunc(to_date('10/1/2014 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) AND trunc(to_date('12/31/2014 00:00:00', 'mm/dd/yyyy hh24:mi:ss'))) -- ensure OpDay for channel is within Period being considered
AND (ix.stoptime BETWEEN trunc(to_date('10/1/2014 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400) AND trunc(to_date('12/31/2014 00:00:00', 'mm/dd/yyyy hh24:mi:ss')) + 1 - (1 / 86400)) -- same as above but needed for partition pruning
AND (ix.starttime = O.opdaystart AND ix.STOPTIME = O.opdaystop) -- Match Opday Start/Stop
--
)
GROUP BY
O.opdaystart ,
O.opdaystop,
l.billdetermcode,
O.FirstDayStarttime,
O.FirstDayStoptime,
O.LastdayStarttime,
O.LastdayStoptime
--
) S
ON (1 = 1)
WHERE (S.opdaystart = i.starttime and S.opdaystop = i.stoptime) -- Interval Blob matches Opday
AND (i.savechannel = S.maxsavechannel)
GROUP BY
S.opdaystart,
b.billdetermcode,
S.FirstDayStarttime,
S.FirstDayStoptime,
S.LastDayStarttime,
S.LastDayStoptime,
i.interval_number,
i.interval_value
ORDER BY I.interval_number;Output A:
TRADEDATE INTERVALDATASOURCE BILLDETERMCODE INTERVAL_NUMBER INTERVAL_VALUE
10/1/2014 DAY COMPETITIVE 1 6688.800464
10/1/2014 DAY COMPETITIVE 2 6620.018807
10/1/2014 DAY COMPETITIVE 3 6519.650601
…
10/2/2014 DAY COMPETITIVE 1 7016.890232
10/2/2014 DAY COMPETITIVE 2 6924.556382
10/2/2014 DAY COMPETITIVE 3 6809.176
…
10/3/2014 DAY COMPETITIVE 1 6468.366323
10/3/2014 DAY COMPETITIVE 2 6352.811683
10/3/2014 DAY COMPETITIVE 3 6196.467315
..
..
12/31/2014 DAY COMPETITIVE 1 5245.238818
12/31/2014 DAY COMPETITIVE 2 5168.246376
12/31/2014 DAY COMPETITIVE 3 5125.244717Desired Output B:
TRADE_DATE INTV1 INTV2 INTV3 DAILY_TOTAL
10/1/2014 6688.800464 6620.018807 6519.650601 SUM of all INTV for 10/1
10/2/2014 7016.890232 6924.556382 6809.176 SUM of all INTV for 10/2
10/3/2014 6468.366323 6352.811683 6196.467315 SUM of all INTV for 10/3
..
..
..
12/31/2014 5245.238818 5168.246376 5125.244717 SUM of all INTV for 12/31
Here is the simple example based on your original output and the PIVOT:
/*
drop table tab1 purge;
create table tab1(TRADEDATE varchar2(10),INTERVAL_NUMBER number, INTERVAL_VALUE number);
insert into tab1 values('10/1/2014',1,6688.800464);
insert into tab1 values('10/1/2014',2,6620.018807);
insert into tab1 values('10/1/2014',3,6519.650601);
insert into tab1 values('10/2/2014',1,7016.890232);
insert into tab1 values('10/2/2014',2,6924.556382);
insert into tab1 values('10/2/2014',3,6809.176);
insert into tab1 values('10/3/2014',1,6468.366323);
insert into tab1 values('10/3/2014',2,6352.811683);
insert into tab1 values('10/3/2014',3,6196.467315);
insert into tab1 values('12/31/2014',1,5245.238818);
insert into tab1 values('12/31/2014',2,5168.246376);
insert into tab1 values('12/31/2014',3,5125.244717);
commit;
*/
select * from (
select tradedate, interval_number, interval_value
from tab1 t
)
pivot
(
max(interval_value)
for interval_number in (1,2,3)
)
order by tradedate
/ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To simplify, I used only 3 intervals to illustrate my example (there are 100 intervals actually).
There's no column INTV1 (the column here is interval_number and I used INTV1 to show as first interval_number value).
Is the pivot still possible without hard coding the values "(1 as INTV1, 2 as INTV2...., 100 as INTV100) and using the interval_number column?
There's no column INTV1 (the column here is interval_number and I used INTV1 to show as first interval_number value).
Is the pivot still possible without hard coding the values "(1 as INTV1, 2 as INTV2...., 100 as INTV100) and using the interval_number column?
>>Is the pivot still possible without hard coding the values
You need to know the number of columns up front. It is a requirement of the SQL engine.
>>There's no column INTV1 (the column here is interval_number and I used INTV1 to show as first interval_number value).
I know that. If you notice in my PIVOT, I aliased the columns so I could add them in the daily total.
You can pivot without the string concatenation but your column names will be 1,2 and 3. Then when you query 1+2+3 you'll get 6.
You need to know the number of columns up front. It is a requirement of the SQL engine.
>>There's no column INTV1 (the column here is interval_number and I used INTV1 to show as first interval_number value).
I know that. If you notice in my PIVOT, I aliased the columns so I could add them in the daily total.
You can pivot without the string concatenation but your column names will be 1,2 and 3. Then when you query 1+2+3 you'll get 6.
ASKER
THanks slightvw! I was able to pivot to output out the rows..
Glad you were able to solve your problem. Don't forget to close the question.
If you followed some other method, please post it and accept it as your answer. If not, please accept the post(s) that helped.
If you followed some other method, please post it and accept it as your answer. If not, please accept the post(s) that helped.
ASKER
slightvw - thanks for your efforts. One thing - when I create a separate table and insert values like you've pointed out the pivot works.
However, the pivot does not SUM correctly when using a nested select (instead of a temp table creation method). Will create a separate question since this is now closed.
However, the pivot does not SUM correctly when using a nested select (instead of a temp table creation method). Will create a separate question since this is now closed.
http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html
If you would like a copy/paste/tested solution, please provide a simplified test case.
I can take your current output and insert it into a single table and work on the expected results if that would help?