Home » SQL & PL/SQL » SQL & PL/SQL » Rows into Columns
icon5.gif  Rows into Columns [message #389643] Tue, 03 March 2009 00:33 Go to next message
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 #389645 is a reply to message #389643] Tue, 03 March 2009 00:35 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Search for PIVOT, you will get lot of Posts about this,

Post DDL and DML also.

Regards,
Ashoka BL
Bengaluru
Re: Rows into Columns [message #389656 is a reply to message #389643] Tue, 03 March 2009 00:51 Go to previous messageGo to next message
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 #389658 is a reply to message #389643] Tue, 03 March 2009 00:52 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member


Quote:

Can we do some other approach?



What have you tried so far ?? Can you post so that we can improve ?

Regards,
Ashoka BL
Bengaluru
Re: Rows into Columns [message #389660 is a reply to message #389643] Tue, 03 March 2009 00:58 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Did you checked the Insert Scripts while generating ?? Its generating errors !!

Regards,
Ashoka BL
Re: Rows into Columns [message #389665 is a reply to message #389643] Tue, 03 March 2009 01:21 Go to previous messageGo to next message
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 #389668 is a reply to message #389665] Tue, 03 March 2009 01:57 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@balaji23_d,

Please go through the Demonstration given by @Maheer. I hope that might help.

If still facing issues, please post the query you have tried.

Regards,
Jo
Re: Rows into Columns [message #389691 is a reply to message #389643] Tue, 03 March 2009 03:53 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 #389701 is a reply to message #389691] Tue, 03 March 2009 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so the query becomes too long

Define "too long".

Regards
Michel
Re: Rows into Columns [message #389704 is a reply to message #389643] Tue, 03 March 2009 05:47 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 #389728 is a reply to message #389704] Tue, 03 March 2009 07:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you mean you want rn 1, 11, 21, 31 and 41 displayed as Col1 ?

I see a pattern there... no need to repeat it for every value
Re: Rows into Columns [message #389794 is a reply to message #389704] Tue, 03 March 2009 10:26 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: questions on code in PL/SQL by Example book
Next Topic: How to login Sqlplus (merged)
Goto Forum:
  


Current Time: Sun Dec 04 19:04:50 CST 2016

Total time taken to generate the page: 0.05191 seconds