Home » SQL & PL/SQL » SQL & PL/SQL » convert column into rows
convert column into rows [message #383788] Fri, 30 January 2009 03:50 Go to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
i have some data in columns and want to convert this data in rows.

Data is like: col1 col2 col3
10 20 30


and i want to show like

col_type value
col1 10
col2 20
col3 30


CREATE TABLE abc(col1 NUMBER,col2 NUMBER,col3 NUMBER)
   
   INSERT INTO (col1,col2,col3) VALUES( 10,20,30);
   
   DROP TABLE abc;

thanks
Re: convert column into rows [message #383790 is a reply to message #383788] Fri, 30 January 2009 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems you have the same problem than mamalik 3 days ago:
http://www.orafaq.com/forum/m/383069/102589/?#msg_383069

Regards
Michel
Re: convert column into rows [message #383791 is a reply to message #383788] Fri, 30 January 2009 04:08 Go to previous messageGo to next message
Serious Sam
Messages: 21
Registered: September 2007
Junior Member

SQL> CREATE TABLE abc(col1 NUMBER,col2 NUMBER,col3 NUMBER)
  2  /

Table created.

SQL> INSERT INTO (col1,col2,col3) VALUES( 10,20,30);
INSERT INTO (col1,col2,col3) VALUES( 10,20,30)
             *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> INSERT INTO abc (col1,col2,col3) VALUES( 10,20,30)
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL> select * from abc
  2  /

      COL1       COL2       COL3
---------- ---------- ----------
        10         20         30

SQL> SELECT DECODE(rn,1,'COL1', 
  2                   2,'COL2', 
  3                   3,'COL3') col_type, 
  4         DECODE(rn,1,col1, 
  5                   2,col2, 
  6                   3,col3) VALUE 
  7  FROM   abc, 
  8         (SELECT LEVEL rn 
  9          FROM   dual 
 10          CONNECT BY LEVEL <= 3) 
 11  /

COL_      VALUE
---- ----------
COL1         10
COL2         20
COL3         30



Thanks,
Sam
Re: convert column into rows [message #383832 is a reply to message #383791] Fri, 30 January 2009 09:00 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
thanks sam ...thats wat i was looking for...grt help:)
Re: convert column into rows [message #383834 is a reply to message #383832] Fri, 30 January 2009 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use IM speak
and next time use "search" link before posting.

Regards
Michel
Re: convert column into rows [message #384010 is a reply to message #383788] Sun, 01 February 2009 14:51 Go to previous messageGo to next message
amr_wafa
Messages: 9
Registered: January 2009
Junior Member
i " think " in the 11g database,there is a built in function that supports converting rows into columns automatically...
but im not 100% sure
Re: convert column into rows [message #384011 is a reply to message #383788] Sun, 01 February 2009 14:54 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
PIVOT?
Re: convert column into rows [message #384063 is a reply to message #384010] Mon, 02 February 2009 01:19 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQB> create table t (col1 integer, col2 integer, col3 integer);

Table created.

SQB> insert into t values (10,20,30);

1 row created.

SQB> select * from t;
      COL1       COL2       COL3
---------- ---------- ----------
        10         20         30

1 row selected.

SQB> select * 
  2  from t 
  3    unpivot (val for col in (col1 as 'col1', col2 as 'col2', col3 as 'col3'))
  4  order by col
  5  /
COL         VAL
---- ----------
col1         10
col2         20
col3         30

3 rows selected.

Regards
Michel

[Updated on: Mon, 02 February 2009 01:19]

Report message to a moderator

Previous Topic: How to solved ORA-04068 error (2 threads merged by bb)
Next Topic: Loop (merged)
Goto Forum:
  


Current Time: Fri Dec 02 16:53:41 CST 2016

Total time taken to generate the page: 0.44611 seconds