Re: convert row to column
Date: Sat, 7 Mar 2009 07:39:58 -0800 (PST)
Message-ID: <ec9f1aa2-3525-420f-a956-3d826ab2677f_at_c11g2000yqj.googlegroups.com>
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_amt2 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.4941717.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 the http://asktom.oracle.com site for one to see a couple of other techniques.
HTH -- Mark D Powell -- Received on Sat Mar 07 2009 - 09:39:58 CST