Home » SQL & PL/SQL » SQL & PL/SQL » how to convert columns to rows in SQL * PLUS
icon14.gif  how to convert columns to rows in SQL * PLUS [message #247836] Wed, 27 June 2007 06:10 Go to next message
muddasani.s
Messages: 10
Registered: June 2007
Junior Member

hi frnds,

give me the explanation for how to convert columns to rows


i.e

ID NAME DES SAL
1 RAJU SE 29000
2 XXX SSE 43000

THAT SHOULD BE

ID 1 2
NAME RAJU XXX
DES SE SSE
SAL 29000 43000

REGARDS
MUDDA

[Updated on: Wed, 27 June 2007 07:39]

Report message to a moderator

Re: how to convert columns to rows in SQL * PLUS [message #247844 is a reply to message #247836] Wed, 27 June 2007 06:33 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

I don't think there is a possibility to achieve the desired result.
Re: how to convert columns to rows in SQL * PLUS [message #247845 is a reply to message #247836] Wed, 27 June 2007 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Post a full test case (create table and insert statements).

Regards
Michel
Re: how to convert columns to rows in SQL * PLUS [message #248010 is a reply to message #247836] Wed, 27 June 2007 15:24 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Check out SQL Snippets: SQL Techniques Tutorials - Columns to Rows.

--
Joe Fuda
SQL Snippets
Re: how to convert columns to rows in SQL * PLUS [message #248041 is a reply to message #248010] Wed, 27 June 2007 18:18 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
example but bit different..
Quote:
How to turn rows into columns

March 13th, 2006 By Eddie Awad

Consider this query:

select job_id, count(*)
from employees
where job_id in ('AD_PRES', 'AD_VP', 'IT_PROG')
group by job_id

It returns this result set:

JOB_ID     COUNT(*)
---------- ----------------------
AD_PRES    1
AD_VP      2
IT_PROG    5                      

3 rows selected

Now, you want to transpose the result set and turn the JOB_ID values into columns in a single row. Here is one way of doing it:

select sum(case when job_id='AD_PRES' then 1 else 0 end) as AD_PRES,
        sum(case when job_id='AD_VP' then 1 else 0 end) as AD_VP,
        sum(case when job_id='IT_PROG' then 1 else 0 end) as IT_PTOG
   from (
      select job_id
      from employees
      where job_id in ('AD_PRES', 'AD_VP', 'IT_PROG'))

The result:

AD_PRES                AD_VP                  IT_PTOG
---------------------- ---------------------- ----------------------
1                      2                      5                      

1 rows selected
Previous Topic: Synonyms
Next Topic: How to call SQLLDR (sql*loader)from oracle stored procedure?
Goto Forum:
  


Current Time: Sat Dec 03 18:14:35 CST 2016

Total time taken to generate the page: 0.09553 seconds