Home » SQL & PL/SQL » SQL & PL/SQL » columns to rows (oracle 10.2.0.1.0 windows xp pro)
columns to rows [message #415641] Tue, 28 July 2009 08:44 Go to next message
kang
Messages: 89
Registered: November 2007
Member
how do I convert columns to rows?

I don't want the way below
because, in this way I have to full table access 4 times because the table has 4 columns.

do you have any ideas?

with tmp as(
  select 'key1' as key, 'Y' as col1, 'N' as col2, 'Y' as col3, 'Y' as col4 from dual union all
  select 'key2', 'N', 'Y', 'N', 'Y' from dual union all
  select 'key3', 'Y', 'Y', 'N', 'N' from dual
), data as (
  select key, decode(col1,'Y','COL1','NA') val from tmp union all
  select key, decode(col2,'Y','COL2','NA') from tmp union all
  select key, decode(col3,'Y','COL3','NA') from tmp union all
  select key, decode(col4,'Y','COL4','NA') from tmp
)
select * from data 
 where val <> 'NA'
 order by key, val
Re: columns to rows [message #415645 is a reply to message #415641] Tue, 28 July 2009 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for PIVOT.

Regards
Michel
Re: columns to rows [message #415649 is a reply to message #415645] Tue, 28 July 2009 09:15 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
Thanks for comments.
As I know pivot query is used to convert rows to columns, like,

select group_col,
       max(decode ...),
       max(decode ...),
       max(decode ...)
from tbl
group by group_col.


I want the opposite.
Re: columns to rows [message #415652 is a reply to message #415649] Tue, 28 July 2009 09:29 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Is google broken for you ?

http://www.google.co.uk/search?hl=en&q=columns+to+rows+oracle&meta=

Regards

Raj
Re: columns to rows [message #415822 is a reply to message #415652] Wed, 29 July 2009 08:00 Go to previous message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
You can achieve this using CASE statements too...
Previous Topic: Questions on grouping.
Next Topic: Taking more time to execute query
Goto Forum:
  


Current Time: Fri Dec 09 15:51:24 CST 2016

Total time taken to generate the page: 0.45415 seconds