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