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

On Mar 7, 8:53*am, NOVA <nova1... (AT) gmail (DOT) com> wrote:
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 the
http://asktom.oracle.com site for one to see a couple of other
techniques.

HTH -- Mark D Powell --

On Mar 7, 6:39*pm, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:


thanks for replay

this is something else.


I want to make rows as column

run my first query then second query (result).

On Mar 7, 5:21*pm, NOVA <nova1... (AT) gmail (DOT) com> wrote:
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 --

On Mar 8, 10:07*pm, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
I have double checked your query and my response produces the same
type of output.

UT1 > @t10

A B C D
---------- ---------- ---------- ----------
5 8 2 9

Also see the following thread:
http://asktom.oracle.com/pls/asktom/...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 --