Home » SQL & PL/SQL » SQL & PL/SQL » Transpose data in Sql (Oracle 10g)
Transpose data in Sql [message #514122] Fri, 01 July 2011 12:48 Go to next message
nathb
Messages: 77
Registered: March 2011
Member
Hello good folks,

I have to transpose data and display it using Sql. How can I do that?

Here is how the data looks in the table now:

Deal Cashflow Date
---------------------------
0007 1228888 01/12/2011
0007 898998 02/12/2011
0007 999999 03/12/2011
0008 888888 01/12/2011
0008 777777 02/12/2011


When I transpose the data, it should look like this:

Deal 01/12/2011 02/12/2011 03/12/2011
0007 1228888 898998 999999
0008 888888 777777


Never had to do this in the past.
Any suggestions please?
thanks
nath
Re: Transpose data in Sql [message #514123 is a reply to message #514122] Fri, 01 July 2011 12:50 Go to previous messageGo to next message
BlackSwan
Messages: 22554
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

http://forums.oracle.com/forums/message.jspa?messageID=3227306
Re: Transpose data in Sql [message #514124 is a reply to message #514123] Fri, 01 July 2011 13:08 Go to previous messageGo to next message
nathb
Messages: 77
Registered: March 2011
Member
Looks like it is very hard to do in oracle. It is so easy in MS Access or Excel.

thanks anyway
nath
Re: Transpose data in Sql [message #514126 is a reply to message #514124] Fri, 01 July 2011 13:19 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So why do you Oracle if it does not fit your need?

Regards
Michel
Re: Transpose data in Sql [message #514128 is a reply to message #514126] Fri, 01 July 2011 13:21 Go to previous messageGo to next message
nathb
Messages: 77
Registered: March 2011
Member
Not my choice Michel. Somone chose it.

I don't hate oracle or anything like that...but I was surprised to see how complex it is, to implement transpose in oracle.

That's all.
Re: Transpose data in Sql [message #514135 is a reply to message #514128] Fri, 01 July 2011 13:58 Go to previous messageGo to next message
ThomasG
Messages: 3090
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The problem with transpose/pivot is that, like in your example, the number of columns is not known at execution time. Which is not a problem for a frontend like the Access report/form generator or the Excel spreadsheet, but it doesn't really fit in a SQL query engine, since it doesn't really fit into the SQL standard, where the columns of the result set have to be known before the query starts to return data.

I can be done with workarounds of course, but it is more involved than doing it in tools that were designed to do it.
Re: Transpose data in Sql [message #514136 is a reply to message #514135] Fri, 01 July 2011 14:00 Go to previous message
nathb
Messages: 77
Registered: March 2011
Member
Thank You Thomas.

Nice reply.
Nath
Previous Topic: generating hourly report from oracle database
Next Topic: Trigger
Goto Forum:
  


Current Time: Thu Jul 31 19:58:29 CDT 2014

Total time taken to generate the page: 0.28982 seconds