Home » SQL & PL/SQL » SQL & PL/SQL » Is It Possible??? (8i,10g)
Is It Possible??? [message #383065] Tue, 27 January 2009 02:51 Go to next message
mamalik
Messages: 270
Registered: November 2008
Location: Pakistan
Senior Member

Dear I have follwing Table

Create Table OrA_faq(col_no1 Varchar2(20),
                     Col_no2 Varchar2(20),
	           Col_No3 Varchar2(20));
SELECT * FROM ORA_FAQ;
IT RETURNS
COL_NO1   COL_NO2     COL_NO3
B            M          R


I want query to display data like this

Col_No1
B
M
R

I know we can achieve it by following query using union

 SELECT col_no1 FROM ORA_FAQ
 union
 SELECT col_no2 FROM ORA_FAQ
 union
 SELECT col_no3 FROM ORA_FAQ


But i dont want to use union. Is there any other solution???

Best Regards.
Asif.


Re: Is It Possible??? [message #383066 is a reply to message #383065] Tue, 27 January 2009 02:54 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Search for "row to column"; one result is this OTN tip.
Re: Is It Possible??? [message #383069 is a reply to message #383065] Tue, 27 January 2009 03:05 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
select decode(line,1,col_no1,2,col_no2,3,col_no3) val
from ora_faq,
     (select 1 line from dual
      union all
      select 2 line from dual
      union all
      select 3 line from dual)
/

Next time post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

[Updated on: Tue, 27 January 2009 03:07]

Report message to a moderator

Previous Topic: Using TABLE functions in select statement (merged 3)
Next Topic: how to alter columns
Goto Forum:
  


Current Time: Thu Feb 13 23:00:14 CST 2025