Re: convert row to column
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 9 Mar 2009 07:19:32 -0700 (PDT)
Message-ID: <1b63a1dc-b1be-46a0-b7ed-94f68d4f9bd2_at_c36g2000yqn.googlegroups.com>
On Mar 8, 10:07 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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.comsiteforone 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 --- Hide quoted text -
>
> - Show quoted text -
Date: Mon, 9 Mar 2009 07:19:32 -0700 (PDT)
Message-ID: <1b63a1dc-b1be-46a0-b7ed-94f68d4f9bd2_at_c36g2000yqn.googlegroups.com>
On Mar 8, 10:07 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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.comsiteforone 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 --- Hide quoted text -
>
> - Show quoted text -
I have double checked your query and my response produces the same type of output.
UT1 > _at_t10
A B C D ---------- ---------- ---------- ----------
5 8 2 9
Also see the following thread:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:31263576751669
It has an example using connect by in 10g+
If this is no what you want then you need to reword your question.
HTH -- Mark D Powell -- Received on Mon Mar 09 2009 - 09:19:32 CDT