Re: convert row to column
From: NOVA <nova1427_at_gmail.com>
Date: Sat, 7 Mar 2009 13:21:32 -0800 (PST)
Message-ID: <266ca843-185c-4b48-815b-e79fd484fdc5_at_v39g2000yqm.googlegroups.com>
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.comsite for one to see a couple of other
> techniques.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -
Date: Sat, 7 Mar 2009 13:21:32 -0800 (PST)
Message-ID: <266ca843-185c-4b48-815b-e79fd484fdc5_at_v39g2000yqm.googlegroups.com>
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.comsite for 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). Received on Sat Mar 07 2009 - 15:21:32 CST