Thread: How do I transpose Rows into Columns?

How do I transpose Rows into Columns?

For example, if I've the data as follows:

Name Value
==== ====
Name1 V11
Name1 V12
Name1 V13
Name2 V21
Name2 V22

I want the OUTPUT to be as follows:

Name1 V11 V12 V13
Name2 V21 V22

Apperciate if anyone can help me out in this.

Waiting for your reply,

Cheers,

hi i did it with my table as yr req. try this in yr table----

(it's very easy)--


10:27:36 SQL> seelct *from test;
SP2-0734: unknown command beginning "seelct *fr..." - rest of line ignored.
10:47:40 SQL> select *from test;

C
-
a
a
a
a
a
b
b
b

8 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=16)
1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=3 Card=8 Bytes
=16)




10:47:48 SQL> select
10:47:57 2 max(decode(rn, 1, col)) col1,
10:47:57 3 max(decode(rn, 2, col)) col2,
10:47:57 4 max(decode(rn, 3, col)) col3,
10:47:57 5 max(decode(rn, 4, col)) col4,
10:47:57 6 max(decode(rn, 5, col)) col5
10:47:57 7 from (
10:47:57 8 select col, row_number() over (partition by col order by col) rn
10:47:57 9 from test
10:47:57 10 order by col)
10:47:57 11 group by col;

C C C C C
- - - - -
a a a a a
b b b

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=30)
1 0 SORT (GROUP BY NOSORT) (Cost=3 Card=2 Bytes=30)
2 1 VIEW (Cost=3 Card=8 Bytes=120)
3 2 WINDOW (SORT) (Cost=3 Card=8 Bytes=16)
4 3 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=3 Card=8
Bytes=16)

try writing some matrix query using decode as shown in sample of previous example.