Home » SQL & PL/SQL » SQL & PL/SQL » Rows into Columns
 Rows into Columns [message #389643] |
Tue, 03 March 2009 00:33  |
balaji23_d
Messages: 123
Registered: February 2007
|
Senior Member |
|
|
Hi all,
please asssist me to convert the rows into cols..
The input is in the structure below:
COLUMN1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
Monday 10 20 30 40 50 60 70 80 90
Tuesday 11 21 31 41 51 61 71 81 91
Wednesday 12 22 32 42 52 62 72 82 92
Thursday 13 23 33 43 53 63 73 83 93
Friday 14 24 34 44 54 64 74 84 94
Saturday 15 25 35 45 55 65 75 85 95
Sunday 16 26 36 46 56 66 76 86 96
The ouput is in the format
COLUMN1 Monday Tuesday Wednesday Thursday Friday Saturday Sunday
COL2 10 11 12 13 14 15 16
COL3 20 21 22 23 24 25 26
COL4 30 31 32 33 34 35 36
COL5 40 41 42 43 44 45 46
COL6 50 51 52 53 54 55 56
COL7 60 61 62 63 64 65 66
COL8 70 71 72 73 74 75 76
COL9 80 81 82 83 84 85 86
COL10 90 91 92 93 94 95 96
|
|
|
|
|
|
| Re: Rows into Columns [message #389656 is a reply to message #389643] |
Tue, 03 March 2009 00:51  |
balaji23_d
Messages: 123
Registered: February 2007
|
Senior Member |
|
|
But I am using the oracle 9i version database
create table rowtocol_test(column1 varchar2(35),
col2 varchar2(35),
col3 varchar2(35),
col4 varchar2(35),
col5 varchar2(35),
col6 varchar2(35),
col7 varchar2(35),
col8 varchar2(35),
col9 varchar2(35),
col10 varchar2(35)
);
insert into rowtocol_test (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10)
values ('MONDAY', '10', '20', '30', '40', '45', '50', '55', '60', '62');
insert into rowtocol_test (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10)
values ('TUESDAY', '12', '13', '14', '15', '16', '17', '18', '18', '21');
insert into rowtocol_test (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10)
values ('WEDNESDAY', '34', '45', '56', '78', '56', '56', '78', '89', '234');
insert into rowtocol_test (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10)
values ('THURSDAY', '30', '31', '32', '33', '34', '35', '36', '38', '39');
insert into rowtocol_test (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10)
values ('FRIDAY', '50', '51', '52', '54', '58', '56', '59', '56', '25');
insert into rowtocol_test (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10)
values ('SATURDAY', '60', '62', '63', '65', '69', '64', '63', '67', '66');
insert into rowtocol_test (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, ROWID)
values ('SUNDAY', '80', '82', '830', '84', '85', '86', '87', '88', '89');
I posted the DDL and DML script above. Can we do some other approach?
|
|
|
|
|
|
|
|
| Re: Rows into Columns [message #389665 is a reply to message #389643] |
Tue, 03 March 2009 01:21 |
balaji23_d
Messages: 123
Registered: February 2007
|
Senior Member |
|
|
please change the last insert scripts
posted script
-------------
insert into rowtocol_test (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, ROWID)
values ('SUNDAY', '80', '82', '830', '84', '85', '86', '87', '88', '89');
changed script
----------------
insert into rowtocol_test (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10)
values ('SUNDAY', '80', '82', '830', '84', '85', '86', '87', '88', '89');
And also please change the first column name as col1
|
|
|
|
|
|
| Re: Rows into Columns [message #389691 is a reply to message #389643] |
Tue, 03 March 2009 03:53 |
balaji23_d
Messages: 123
Registered: February 2007
|
Senior Member |
|
|
ya I tried this way, it gives the proper result...But in case some tables having more than 50 cols.. so every time we have to mention that column name in the pivot and then validate it...so the query becomes too long... can anyone give me some other approach?
SQL> select * from (select col1,col2
2 from rowtocol_test)
3 pivot (max(col2) for col1 in ('MON','TUES','WED'))
4 union all
5 select * from (select col1,COL3
6 from rowtocol_test)
7 pivot (max(col3) for col1 in ('MON','TUES','WED'))
8 union all
9 select * from (select col1,COL4
10 from rowtocol_test)
11 pivot (max(col4) for col1 in ('MON','TUES','WED'))
12 union all
13 select * from (select col1,COL5
14 from rowtocol_test)
15 pivot (max(col5) for col1 in ('MON','TUES','WED'))
16 union all
17 select * from (select col1,COL6
18 from rowtocol_test)
19 pivot (max(col6) for col1 in ('MON','TUES','WED'))
20 union all
21 select * from (select col1,COL7
22 from rowtocol_test)
23 pivot (max(col7) for col1 in ('MON','TUES','WED'))
24 union all
25 select * from (select col1,COL8
26 from rowtocol_test)
27 pivot (max(col8) for col1 in ('MON','TUES','WED'))
28 union all
29 select * from (select col1,COL9
30 from rowtocol_test)
31 pivot (max(col9) for col1 in ('MON','TUES','WED'))
32 union all
33 select * from (select col1,COL10
34 from rowtocol_test)
35 pivot (max(col10) for col1 in ('MON','TUES','WED'))
36 ;
The o/p shows:
'MON' 'TUES' 'WED'
----------------------------------- ----------------------------------- -----------------------------------
10 12 34
20 13 45
30 14 56
40 15 78
45 16 56
50 17 56
55 18 78
60 18 89
62 21 234
|
|
|
|
| Re: Rows into Columns [message #389697 is a reply to message #389643] |
Tue, 03 March 2009 04:50 |
|
|
I believe PIVOT is there above 10g.
Can anybody let us know how to do it without PIVOT ?
SELECT DECODE (rn,
1, 'Col1',
2, 'Col2',
3, 'col3',
4, 'col4',
5, 'col5',
6, 'col6',
7, 'col7',
8, 'col8',
9, 'col9',
10, 'col10'
) cols
FROM (SELECT LEVEL rn
FROM DUAL
CONNECT BY LEVEL <= 10)
I used the above code to get the first part,but still a long way, anybody have any different idea ?
Regards,
Ashoka BL
Bengaluru
|
|
|
|
|
|
| Re: Rows into Columns [message #389704 is a reply to message #389643] |
Tue, 03 March 2009 05:47 |
|
|
Michel,
It is not so simple query for newbie like me, can you please give some hint on how to implement this without using UNPIVOT.
Regards,
Ashoka BL
Bengaluru
|
|
|
|
|
|
| Re: Rows into Columns [message #389794 is a reply to message #389704] |
Tue, 03 March 2009 10:26 |
 |
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> select * from t order by 1;
COL0 COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9
---- ---- ---- ---- ---- ---- ---- ---- ---- ----
FRI 50 51 52 54 58 56 59 56 25
MON 10 20 30 40 45 50 55 60 62
SAT 60 62 63 65 69 64 63 67 66
SUN 80 82 830 84 85 86 87 88 89
THU 30 31 32 33 34 35 36 38 39
TUE 12 13 14 15 16 17 18 18 21
WED 34 45 56 78 56 56 78 89 234
7 rows selected.
SQL> with
2 lines as ( select level line from dual connect by level < 10 ),
3 data as (
4 select col0, line,
5 dense_rank() over (order by col0) rn,
6 decode(line, 1, COL1, 2, COL2, 3, COL3, 4, COL4, 5, COL5,
7 6, COL6, 7, COL7, 8, COL8, 9, COL9) val
8 from t, lines
9 )
10 select decode(line, 1, 'COL1', 2, 'COL2', 3, 'COL3', 4, 'COL4', 5, 'COL5',
11 6, 'COL6', 7, 'COL7', 8, 'COL8', 9, 'COL9') col0,
12 max(decode(rn,1,val)) "FRI",
13 max(decode(rn,2,val)) "MON",
14 max(decode(rn,3,val)) "SAT",
15 max(decode(rn,4,val)) "SUN",
16 max(decode(rn,5,val)) "THU",
17 max(decode(rn,6,val)) "TUE",
18 max(decode(rn,7,val)) "WED"
19 from data
20 group by decode(line, 1, 'COL1', 2, 'COL2', 3, 'COL3', 4, 'COL4', 5, 'COL5',
21 6, 'COL6', 7, 'COL7', 8, 'COL8', 9, 'COL9')
22 order by 1
23 /
COL0 FRI MON SAT SUN THU TUE WED
---- ---- ---- ---- ---- ---- ---- ----
COL1 50 10 60 80 30 12 34
COL2 51 20 62 82 31 13 45
COL3 52 30 63 830 32 14 56
COL4 54 40 65 84 33 15 78
COL5 58 45 69 85 34 16 56
COL6 56 50 64 86 35 17 56
COL7 59 55 63 87 36 18 78
COL8 56 60 67 88 38 18 89
COL9 25 62 66 89 39 21 234
9 rows selected.
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Fri Jul 11 09:33:35 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
