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.

 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;

Open in new window


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.244717

Open in new window


Desired 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

Open in new window

steve2312Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Seems like a simple PIVOT:
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?
0
slightwv (䄆 Netminder) Commented:
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
/

Open in new window

0
slightwv (䄆 Netminder) Commented:
Forgot the daily total:
/*

drop table tab1 purge;
create table tab1(TRADEDATE date,INTERVAL_NUMBER number, INTERVAL_VALUE number);
insert into tab1 values(to_date('10/1/2014','MM/DD/YYYY'),1,6688.800464);
insert into tab1 values(to_date('10/1/2014','MM/DD/YYYY'),2,6620.018807);
insert into tab1 values(to_date('10/1/2014','MM/DD/YYYY'),3,6519.650601);
insert into tab1 values(to_date('10/2/2014','MM/DD/YYYY'),1,7016.890232);
insert into tab1 values(to_date('10/2/2014','MM/DD/YYYY'),2,6924.556382);
insert into tab1 values(to_date('10/2/2014','MM/DD/YYYY'),3,6809.176);
insert into tab1 values(to_date('10/3/2014','MM/DD/YYYY'),1,6468.366323);
insert into tab1 values(to_date('10/3/2014','MM/DD/YYYY'),2,6352.811683);
insert into tab1 values(to_date('10/3/2014','MM/DD/YYYY'),3,6196.467315);
insert into tab1 values(to_date('12/31/2014','MM/DD/YYYY'),1,5245.238818);
insert into tab1 values(to_date('12/31/2014','MM/DD/YYYY'),2,5168.246376);
insert into tab1 values(to_date('12/31/2014','MM/DD/YYYY'),3,5125.244717);
commit;

*/

select TRADEDATE,INTV1,INTV2,INTV3, INTV1+INTV2+INTV3 daily_total  from (
   select tradedate, interval_number my_interval, interval_value
   from tab1 t
)
pivot 
(
   max(interval_value)
   for my_interval in (1 as "INTV1", 2 as "INTV2", 3 as "INTV3")
)
order by tradedate
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

steve2312Author Commented:
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?
0
slightwv (䄆 Netminder) Commented:
>>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.
0
steve2312Author Commented:
THanks slightvw! I was able to pivot to output out the rows..
0
slightwv (䄆 Netminder) Commented:
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.
0
steve2312Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PL/SQL

From novice to tech pro — start learning today.