Home » SQL & PL/SQL » SQL & PL/SQL » Row to column conversion (9.2.0.7)
Row to column conversion [message #333997] Tue, 15 July 2008 02:48 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi All,

I am having the requirement to convert row into column in SQL using lookup concept. I have used the logic long back. Could you please help me to recollect it?

Thanks in advance

Re: Row to column conversion [message #333999 is a reply to message #333997] Tue, 15 July 2008 02:54 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Could you please help me to recollect it?
I always find that if I stop thinking about something that I am trying to remember, I seem to remember it easier. Try that.
Re: Row to column conversion [message #334000 is a reply to message #333999] Tue, 15 July 2008 02:58 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
I wrote column to row but same logic is not worjking here ..

here is the code for column to row

scott@DEV> select job,
  2         max( decode( deptno, 10, cnt, null ) ) dept_10,
  3         max( decode( deptno, 20, cnt, null ) ) dept_20,
  4         max( decode( deptno, 30, cnt, null ) ) dept_30,
  5         max( decode( deptno, 40, cnt, null ) ) dept_40
  6    from ( select job, deptno, count(*) cnt
  7             from emp
  8            group by job, deptno )
  9   group by job
 10  /


So please give me a query for the requirement ..

Thank you
Re: Row to column conversion [message #334002 is a reply to message #334000] Tue, 15 July 2008 03:04 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
OMG! Are you EVER going to learn? It would appear not. You are clearly lazy and verging on incompetent. USE THE SEARCH CAPABILITY OF THIS FORUM AND THE WWW IN GENERAL If you put the word Oracle in front of your subject line and search on google you will find a LOT of answers.
Re: Row to column conversion [message #334006 is a reply to message #334002] Tue, 15 July 2008 03:19 Go to previous message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
SELECT Year,       
   MAX(CASE WHEN Quarter = 1           
    THEN Results END) AS Q1,       
   MAX(CASE WHEN Quarter = 2           
    THEN Results END) AS Q2,       
   MAX(CASE WHEN Quarter = 3           
    THEN Results END) AS Q3,       
   MAX(CASE WHEN Quarter = 4           
    THEN Results END) AS Q4
FROM Sales
GROUP BY Year
Previous Topic: To delete a table from another table which has duplicate values in it
Next Topic: Multilevel subtotal of columns
Goto Forum:
  


Current Time: Tue Feb 11 16:55:49 CST 2025