Home » SQL & PL/SQL » SQL & PL/SQL » Transpose (11.2 g)
Transpose [message #569740] Wed, 31 October 2012 19:16 Go to next message
primer2020
Messages: 32
Registered: August 2012
Member
Hello,

I have this select statement that out puts exactly one row and three columns.

select 'One' as A, 'Two' as B, 'Three' as C from dual
 
 A         B         C
----      ----     ----
One       Two      Three


I would like to have this output:

One
Two
Three

One possible solution is:
select 'One' as A  from dual
 
union all
 
select  'Two' as B  from dual
 
union all
 
select  'Three' as C from dual
 


I would like to have a different solution/concept.

Thank you
Re: Transpose [message #569741 is a reply to message #569740] Wed, 31 October 2012 20:12 Go to previous messageGo to next message
BlackSwan
Messages: 22489
Registered: January 2009
Senior Member
https://forums.oracle.com/forums/thread.jspa?messageID=10668569#10668569
Re: Transpose [message #569748 is a reply to message #569740] Thu, 01 November 2012 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 58521
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You need a row generator, see this post:
http://www.orafaq.com/forum/mv/msg/95011/493456/102589/#msg_493456

Regards
Michel

Re: Transpose [message #569781 is a reply to message #569748] Thu, 01 November 2012 08:33 Go to previous messageGo to next message
_jum
Messages: 486
Registered: February 2008
Senior Member
In this special case you could use:
SELECT level,
       TO_CHAR(TO_DATE(level,'J'),'Jsp') words 
  FROM dual CONNECT BY level<=3;

level   words
---------------
1	One
2	Two
3	Three

For explanation have a look at Format Models esp. J and SP
Re: Transpose [message #569788 is a reply to message #569781] Thu, 01 November 2012 11:19 Go to previous messageGo to next message
primer2020
Messages: 32
Registered: August 2012
Member
Thank you!!!
Re: Transpose [message #569791 is a reply to message #569788] Thu, 01 November 2012 11:26 Go to previous message
Michel Cadot
Messages: 58521
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you didn't read the links I posted. Lazy boy...
Don't count on me to help you in your futur questions as I and anyone but _jum do not deserve your thanks (see your previous topics).
You will have to wait for _jum to get help now.

Regards
Michel
Previous Topic: Sample Extraction
Next Topic: Timestamp in mail generated from oracle is different
Goto Forum:
  


Current Time: Wed Jul 23 23:46:07 CDT 2014

Total time taken to generate the page: 0.12372 seconds