Home » SQL & PL/SQL » SQL & PL/SQL » Transpose single column into a row (oracle9.2.0.3)
Transpose single column into a row [message #422425] Wed, 16 September 2009 06:20 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
In a table there is only one column which have 8 values 5,6,7,8
,9,3,2,2



Select l from test;

l
--
5
6
7
8
9
3
2
2

how can I convert it into columns like below

5 6 7 8 9 3 2 2


I can do just the opposite. Need your help!
Re: Transpose single column into a row [message #422427 is a reply to message #422425] Wed, 16 September 2009 06:27 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
please about PIVOT queries
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:766825833740

I am completely out of work Wink
SELECT   MAX (DECODE (r, 1, i, NULL)) a, MAX (DECODE (r, 2, i, NULL)) b,
         MAX (DECODE (r, 3, i, NULL)) c, MAX (DECODE (r, 4, i, NULL)) d,
         MAX (DECODE (r, 5, i, NULL)) e, MAX (DECODE (r, 6, i, NULL)) f,
         MAX (DECODE (r, 7, i, NULL)) g, MAX (DECODE (r, 8, i, NULL)) h
    FROM (SELECT i, ROWNUM r
            FROM TEST)

the reverse is pretty easy ..Homework for you Surprised

Question for all : Is there any method to have any number of columns and convert it to a row ?? say I can have 4 or 5 columns or 18 columns..I think no..But if Yes please answer

[Updated on: Wed, 16 September 2009 06:44]

Report message to a moderator

Re: Transpose single column into a row [message #422430 is a reply to message #422425] Wed, 16 September 2009 07:08 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Sorry I am completely out of work .
There are two ways of doing reverse(I can think of)
1>
with test1 as 
(
SELECT   MAX (DECODE (r, 1, i, NULL)) a, MAX (DECODE (r, 2, i, NULL)) b,
         MAX (DECODE (r, 3, i, NULL)) c, MAX (DECODE (r, 4, i, NULL)) d,
         MAX (DECODE (r, 5, i, NULL)) e, MAX (DECODE (r, 6, i, NULL)) f,
         MAX (DECODE (r, 7, i, NULL)) g, MAX (DECODE (r, 8, i, NULL)) h
    FROM (SELECT i, ROWNUM r
            FROM TEST))
select a from test1
union all
select b from test1
union all
select c from test1
union all
select d from test1
union all
select e from test1
union all
select f from test1
union all
select g from test1
union all
select h from test1
	

2>

with test1 as 
(
SELECT   MAX (DECODE (r, 1, i, NULL)) a, MAX (DECODE (r, 2, i, NULL)) b,
         MAX (DECODE (r, 3, i, NULL)) c, MAX (DECODE (r, 4, i, NULL)) d,
         MAX (DECODE (r, 5, i, NULL)) e, MAX (DECODE (r, 6, i, NULL)) f,
         MAX (DECODE (r, 7, i, NULL)) g, MAX (DECODE (r, 8, i, NULL)) h
    FROM (SELECT i, ROWNUM r
            FROM TEST))
   SELECT REGEXP_SUBSTR (a||','||b||','||c||','||d||','||e||','||f||','||g||','||h, '[^,]+', 1, LEVEL) a
      FROM test1
CONNECT BY 
REGEXP_SUBSTR (a||','||b||','||c||','||d||','||e||','||f||','||g||','||h, '[^,]+', 1, LEVEL) 
IS NOT NULL

[Updated on: Wed, 16 September 2009 07:44] by Moderator

Report message to a moderator

Re: Transpose single column into a row [message #422431 is a reply to message #422430] Wed, 16 September 2009 07:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Another possible option would be Tom Kyte's STRAGG, which adds a new user defined aggregate function to Oracle.

Re: Transpose single column into a row [message #422432 is a reply to message #422430] Wed, 16 September 2009 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Question for all : Is there any method to have any number of columns and convert it to a row ?? say I can have 4 or 5 columns or 18 columns..I think no..But if Yes please answer

As you are completly out of work, try to find a solution and post it. It would be much more useful than answering basic homework question already answered hundred times. Wink Smile Laughing (as you like smileys).

Regards
Michel

[Updated on: Wed, 16 September 2009 07:42]

Report message to a moderator

Re: Transpose single column into a row [message #422441 is a reply to message #422432] Wed, 16 September 2009 08:41 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
Question for all : Is there any method to have any number of columns and convert it to a row ?? say I can have 4 or 5 columns or 18 columns..I think no..But if Yes please answer


I think that can be easily done with the use of userdefined functions but is there any pure SQL query solution for this?

in 11g they have introduced a new PIVOT clause but I dont have 11g installation Sad
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html

[Updated on: Wed, 16 September 2009 08:43]

Report message to a moderator

Re: Transpose single column into a row [message #422442 is a reply to message #422441] Wed, 16 September 2009 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ayush_anand wrote on Wed, 16 September 2009 15:41
Quote:
Question for all : Is there any method to have any number of columns and convert it to a row ?? say I can have 4 or 5 columns or 18 columns..I think no..But if Yes please answer


I think that can be easily done with the use of userdefined functions but is there any pure SQL query solution for this?

in 11g they have introduced a new PIVOT clause but I dont have 11g installation Sad
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html

So show us how you do it with this (pure SQL or not).

Regards
Michel
Re: Transpose single column into a row [message #422530 is a reply to message #422442] Thu, 17 September 2009 03:45 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SELECT column_name(s)
FROM table_name


In a Select statement the name of columns has to be specified.Isnt it?

So this problem can de solved only with use of Dynamic Sql not with Static one.

Re: Transpose single column into a row [message #422532 is a reply to message #422530] Thu, 17 September 2009 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ayush_anand wrote on Thu, 17 September 2009 10:45
SELECT column_name(s)
FROM table_name

In a Select statement the name of columns has to be specified.Isnt it?

So this problem can de solved only with use of Dynamic Sql not with Static one.

Please SHOW us.
You posted code for obvious case, post code for this one.

Regards
Michel

Re: Transpose single column into a row [message #422534 is a reply to message #422532] Thu, 17 September 2009 05:43 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I am really sorry as this thing seems impossible Sad
Needs maximum number of rows to be defined

[Updated on: Thu, 17 September 2009 05:45]

Report message to a moderator

Re: Transpose single column into a row [message #422541 is a reply to message #422534] Thu, 17 September 2009 06:14 Go to previous message
QuickGun
Messages: 8
Registered: September 2009
Location: SG
Junior Member
Not sure if had gotten you question right!
I have tried my best to fix your problem.

Good luck


CREATE TABLE t_test
(col1 VARCHAR2(10));

INSERT INTO t_test VALUES ('1');
INSERT INTO t_test VALUES ('2');
INSERT INTO t_test VALUES ('3');
INSERT INTO t_test VALUES ('4');
INSERT INTO t_test VALUES ('5');
INSERT INTO t_test VALUES ('6');
INSERT INTO t_test VALUES ('7');
INSERT INTO t_test VALUES ('8');
COMMIT;
----------------------------------
DECLARE 
CURSOR cur_align IS
SELECT col1 FROM t_test;

vOutput VARCHAR2(100);

BEGIN
FOR vEach IN  cur_align LOOP
	vOutput := vOutput||' '||vEach.col1;
END LOOP;
dbms_output.put_line('vOutput '|| vOutput);
END;
 
Previous Topic: Procedure utl_smtp.write_raw_data
Next Topic: Insert or update using dbms_xmlsave
Goto Forum:
  


Current Time: Wed Dec 07 04:38:05 CST 2016

Total time taken to generate the page: 0.09873 seconds