Re: convert row to column
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sun, 8 Mar 2009 19:07:21 -0700 (PDT)
Message-ID: <6ce6769b-c71e-4074-88e7-fd053c56a7ba_at_v38g2000yqb.googlegroups.com>
On Mar 7, 5:21 pm, NOVA <nova1..._at_gmail.com> wrote:
> On Mar 7, 6:39 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Mar 7, 8:53 am, NOVA <nova1..._at_gmail.com> wrote:
>
> > > Dear all,
>
> > > I have this table as as example
>
> > > select 'A' C1, 5 C2 from dual union all
> > > select 'B', 8 from dual union all
> > > select 'C', 2 from dual union all
> > > select 'D', 9 from dual
>
> > > the query below make the rows as column, I want another way to make
> > > it
> > > by good performance becase if I applay the query on alot of rows it
> > > will take long of time.
>
> > > SELECT
> > > MAX(CASE WHEN C1='A' THEN C2 END) A,
> > > MAX(CASE WHEN C1='B' THEN C2 END) B,
> > > MAX(CASE WHEN C1='C' THEN C2 END) C,
> > > MAX(CASE WHEN C1='D' THEN C2 END) D
> > > FROM
> > > (
> > > select 'A' C1, 5 C2 from dual union all
> > > select 'B', 8 from dual union all
> > > select 'C', 2 from dual union all
> > > select 'D', 9 from dual
> > > )
>
> > > there is another way?
>
> > > thx
>
> > > Best Regards
>
> > This is known a pivoting and you can find numerous ways to do this by
> > searching on the term, pivot table.
>
> > The old way was in fact to use a table to help perform the pivoting of
> > the rows into columns.
>
> > SQL> --
> > SQL> -- select sample data
> > SQL> select process_date, userid_alias, reqstd_amt
> > 2 from war_rpt_admin_stats ws
> > 3 where ws.userid_alias = 'DISP TOTAL'
> > 4 and rownum < 6
> > 5 /
>
> > PROCESS_D USERID_ALIAS
> > REQSTD_AMT
> > --------- ------------------------------
> > ----------
> > 25-JUN-03 DISP TOTAL
> > 1950333.76
> > 25-JUN-03 DISP TOTAL
> > 357911.44
> > 25-JUN-03 DISP TOTAL
> > 4375403.45
> > 25-JUN-03 DISP TOTAL
> > 982284.49
> > 25-JUN-03 DISP TOTAL
> > 41717.87
>
> > SQL> --
> > SQL> -- clean up prior runs
> > SQL> drop table aeikv_pivot
> > 2 /
>
> > Table dropped.
>
> > SQL> --
> > SQL> -- create the pivot table, 1 lable plus 1 col for each
> > desired column
> > SQL> create table aeikv_pivot (
> > 2 value varchar2(1) not null
> > 3 ,a varchar2(1) not null
> > 4 ,e varchar2(1) not null
> > 5 ,i varchar2(1) not null
> > 6 ,k varchar2(1) not null
> > 7 ,v varchar2(1) not null
> > 8 )
> > 9 tablespace usr
> > 10 /
>
> > Table created.
>
> > SQL> --
> > SQL> -- set up pivot values
> > SQL> insert into aeikv_pivot
> > 2 values ('A','1','0','0','0','0')
> > 3 /
>
> > 1 row created.
>
> > SQL> insert into aeikv_pivot
> > 2 values ('E','0','1','0','0','0')
> > 3 /
>
> > 1 row created.
>
> > SQL> insert into aeikv_pivot
> > 2 values ('I','0','0','1','0','0')
> > 3 /
>
> > 1 row created.
>
> > SQL> insert into aeikv_pivot
> > 2 values ('K','0','0','0','1','0')
> > 3 /
>
> > 1 row created.
>
> > SQL> insert into aeikv_pivot
> > 2 values ('V','0','0','0','0','1')
> > 3 /
>
> > 1 row created.
>
> > SQL> -- display pivot
> > SQL> select * from aeikv_pivot
> > 2 /
>
> > V A E I K
> > V
> > - - - - -
> > -
> > A 1 0 0 0
> > 0
> > E 0 1 0 0
> > 0
> > I 0 0 1 0
> > 0
> > K 0 0 0 1
> > 0
> > V 0 0 0 0
> > 1
>
> > SQL> --
> > SQL> -- perform pivoting action
> > SQL> select
> > 2 process_date
> > 3 ,sum(ws.reqstd_amt * pt.a) as "A Dollars"
> > 4 ,sum(ws.reqstd_amt * pt.e) as "E Dollars"
> > 5 ,sum(ws.reqstd_amt * pt.i) as "I Dollars"
> > 6 ,sum(ws.reqstd_amt * pt.k) as "K Dollars"
> > 7 ,sum(ws.reqstd_amt * pt.v) as "V Dollars"
> > 8 from
> > 9 war_rpt_admin_stats ws
> > 10 ,aeikv_pivot pt
> > 11 where ws.userid_alias = 'DISP TOTAL'
> > 12 and ws.disp_code = pt.value
> > 13 group by process_date
> > 14 /
>
> > PROCESS_D A Dollars E Dollars I Dollars K Dollars V
> > Dollars
> > --------- ---------- ---------- ---------- ----------
> > ----------
> > 25-JUN-03 1950333.76 357911.44 4375403.45 982284.49
> > 41717.87
> > 26-JUN-03 1724270.12 264826.83 5074678.6 877965.21
> > 41717.87
> > 27-JUN-03 1214516.09 264826.83 5726897.57 754109.41
> > 21691.91
> > 28-JUN-03 847690.86 264826.83 5272895.42 751705.77
> > 24827.53
> > 29-JUN-03 939055.38 264826.83 5040791.8 768580.69
> > 24827.53
> > 30-JUN-03 987488.78 264826.83 5810850.87 755920.32
> > 24278.85
> > 01-JUL-03 1269530.59 264826.83 5535902.04 803528.2
> > 24278.85
> > 02-JUL-03 1352037.37 260431.29 6017766.48 806775.62
> > 24643.13
> > 03-JUL-03 1472210.62 260431.29 6404842.43 804643.18
> > 29463.71
> > 04-JUL-03 1513739.11 260431.29 6248600.88 807601.11
> > 29463.71
> > 05-JUL-03 1693791.83 260431.29 5579727.96 817450.14
> > 32996.21
> > 06-JUL-03 1490785.28 260431.29 5555402.31 820988.88
> > 34259.52
> > 07-JUL-03 1492613.18 253855.75 5558814.29 821235.93
> > 37720.17
>
> > 13 rows selected.
>
> > SQL> spool off
>
> > The above method has proved fast enough in our usage. Search thehttp://asktom.oracle.comsitefor one to see a couple of other
> > techniques.
>
> > HTH -- Mark D Powell --- Hide quoted text -
>
> > - Show quoted text -
>
> thanks for replay
>
> this is something else.
>
> I want to make rows as column
>
> run my first query then second query (result).- Hide quoted text -
>
> - Show quoted text -
Date: Sun, 8 Mar 2009 19:07:21 -0700 (PDT)
Message-ID: <6ce6769b-c71e-4074-88e7-fd053c56a7ba_at_v38g2000yqb.googlegroups.com>
On Mar 7, 5:21 pm, NOVA <nova1..._at_gmail.com> wrote:
> On Mar 7, 6:39 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Mar 7, 8:53 am, NOVA <nova1..._at_gmail.com> wrote:
>
> > > Dear all,
>
> > > I have this table as as example
>
> > > select 'A' C1, 5 C2 from dual union all
> > > select 'B', 8 from dual union all
> > > select 'C', 2 from dual union all
> > > select 'D', 9 from dual
>
> > > the query below make the rows as column, I want another way to make
> > > it
> > > by good performance becase if I applay the query on alot of rows it
> > > will take long of time.
>
> > > SELECT
> > > MAX(CASE WHEN C1='A' THEN C2 END) A,
> > > MAX(CASE WHEN C1='B' THEN C2 END) B,
> > > MAX(CASE WHEN C1='C' THEN C2 END) C,
> > > MAX(CASE WHEN C1='D' THEN C2 END) D
> > > FROM
> > > (
> > > select 'A' C1, 5 C2 from dual union all
> > > select 'B', 8 from dual union all
> > > select 'C', 2 from dual union all
> > > select 'D', 9 from dual
> > > )
>
> > > there is another way?
>
> > > thx
>
> > > Best Regards
>
> > This is known a pivoting and you can find numerous ways to do this by
> > searching on the term, pivot table.
>
> > The old way was in fact to use a table to help perform the pivoting of
> > the rows into columns.
>
> > SQL> --
> > SQL> -- select sample data
> > SQL> select process_date, userid_alias, reqstd_amt
> > 2 from war_rpt_admin_stats ws
> > 3 where ws.userid_alias = 'DISP TOTAL'
> > 4 and rownum < 6
> > 5 /
>
> > PROCESS_D USERID_ALIAS
> > REQSTD_AMT
> > --------- ------------------------------
> > ----------
> > 25-JUN-03 DISP TOTAL
> > 1950333.76
> > 25-JUN-03 DISP TOTAL
> > 357911.44
> > 25-JUN-03 DISP TOTAL
> > 4375403.45
> > 25-JUN-03 DISP TOTAL
> > 982284.49
> > 25-JUN-03 DISP TOTAL
> > 41717.87
>
> > SQL> --
> > SQL> -- clean up prior runs
> > SQL> drop table aeikv_pivot
> > 2 /
>
> > Table dropped.
>
> > SQL> --
> > SQL> -- create the pivot table, 1 lable plus 1 col for each
> > desired column
> > SQL> create table aeikv_pivot (
> > 2 value varchar2(1) not null
> > 3 ,a varchar2(1) not null
> > 4 ,e varchar2(1) not null
> > 5 ,i varchar2(1) not null
> > 6 ,k varchar2(1) not null
> > 7 ,v varchar2(1) not null
> > 8 )
> > 9 tablespace usr
> > 10 /
>
> > Table created.
>
> > SQL> --
> > SQL> -- set up pivot values
> > SQL> insert into aeikv_pivot
> > 2 values ('A','1','0','0','0','0')
> > 3 /
>
> > 1 row created.
>
> > SQL> insert into aeikv_pivot
> > 2 values ('E','0','1','0','0','0')
> > 3 /
>
> > 1 row created.
>
> > SQL> insert into aeikv_pivot
> > 2 values ('I','0','0','1','0','0')
> > 3 /
>
> > 1 row created.
>
> > SQL> insert into aeikv_pivot
> > 2 values ('K','0','0','0','1','0')
> > 3 /
>
> > 1 row created.
>
> > SQL> insert into aeikv_pivot
> > 2 values ('V','0','0','0','0','1')
> > 3 /
>
> > 1 row created.
>
> > SQL> -- display pivot
> > SQL> select * from aeikv_pivot
> > 2 /
>
> > V A E I K
> > V
> > - - - - -
> > -
> > A 1 0 0 0
> > 0
> > E 0 1 0 0
> > 0
> > I 0 0 1 0
> > 0
> > K 0 0 0 1
> > 0
> > V 0 0 0 0
> > 1
>
> > SQL> --
> > SQL> -- perform pivoting action
> > SQL> select
> > 2 process_date
> > 3 ,sum(ws.reqstd_amt * pt.a) as "A Dollars"
> > 4 ,sum(ws.reqstd_amt * pt.e) as "E Dollars"
> > 5 ,sum(ws.reqstd_amt * pt.i) as "I Dollars"
> > 6 ,sum(ws.reqstd_amt * pt.k) as "K Dollars"
> > 7 ,sum(ws.reqstd_amt * pt.v) as "V Dollars"
> > 8 from
> > 9 war_rpt_admin_stats ws
> > 10 ,aeikv_pivot pt
> > 11 where ws.userid_alias = 'DISP TOTAL'
> > 12 and ws.disp_code = pt.value
> > 13 group by process_date
> > 14 /
>
> > PROCESS_D A Dollars E Dollars I Dollars K Dollars V
> > Dollars
> > --------- ---------- ---------- ---------- ----------
> > ----------
> > 25-JUN-03 1950333.76 357911.44 4375403.45 982284.49
> > 41717.87
> > 26-JUN-03 1724270.12 264826.83 5074678.6 877965.21
> > 41717.87
> > 27-JUN-03 1214516.09 264826.83 5726897.57 754109.41
> > 21691.91
> > 28-JUN-03 847690.86 264826.83 5272895.42 751705.77
> > 24827.53
> > 29-JUN-03 939055.38 264826.83 5040791.8 768580.69
> > 24827.53
> > 30-JUN-03 987488.78 264826.83 5810850.87 755920.32
> > 24278.85
> > 01-JUL-03 1269530.59 264826.83 5535902.04 803528.2
> > 24278.85
> > 02-JUL-03 1352037.37 260431.29 6017766.48 806775.62
> > 24643.13
> > 03-JUL-03 1472210.62 260431.29 6404842.43 804643.18
> > 29463.71
> > 04-JUL-03 1513739.11 260431.29 6248600.88 807601.11
> > 29463.71
> > 05-JUL-03 1693791.83 260431.29 5579727.96 817450.14
> > 32996.21
> > 06-JUL-03 1490785.28 260431.29 5555402.31 820988.88
> > 34259.52
> > 07-JUL-03 1492613.18 253855.75 5558814.29 821235.93
> > 37720.17
>
> > 13 rows selected.
>
> > SQL> spool off
>
> > The above method has proved fast enough in our usage. Search thehttp://asktom.oracle.comsitefor one to see a couple of other
> > techniques.
>
> > HTH -- Mark D Powell --- Hide quoted text -
>
> > - Show quoted text -
>
> thanks for replay
>
> this is something else.
>
> I want to make rows as column
>
> run my first query then second query (result).- Hide quoted text -
>
> - Show quoted text -
The code I posted turns rows of data from the target table into columns in the output. I really did not look closely at your posted query so much as I responded to your words which is a common request. I will try to find time tomorrow to look closer and see from your output what you really want.
- Mark D Powell --