Home » SQL & PL/SQL » SQL & PL/SQL » Horizontal to Vertical Transpose. (Oracle 10g; Windows)
Horizontal to Vertical Transpose. [message #397744] Mon, 13 April 2009 13:43 Go to next message
Nirmala
Messages: 43
Registered: October 2004
Member
I have a table which has 12 columns. For every row in that table i want the output to be spilt into 12 rows.

create table monthly
(jan number(10),
feb number(10),
mar number(10),
apr number(10),
may number(10),
jun number(10),
jul number(10),
aug number(10),
sep number(10),
oct number(10),
nov number(10),
dec number(10));

insert 
  into monthly 
values (1000,
        1000,
        1000,
        1000,
        1000,
        1000,
        1000,
        1000,
        1000,
        1000,
        1000,
        1000);



I want the output from this table as 12 rows as given below. Is there anyway i can get it using a SQL Query?

Jan 1000
Feb 1000
Mar 1000
Apr 1000
May 1000
Jun 1000
Jul 1000
Aug 1000
Sep 1000
Oct 1000
Nov 1000
Dec 1000


Re: Horizontal to Vertical Transpose. [message #397745 is a reply to message #397744] Mon, 13 April 2009 13:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
search this forum for PIVOT
Re: Horizontal to Vertical Transpose. [message #397746 is a reply to message #397745] Mon, 13 April 2009 13:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
... and row generator, or "columns to rows"

Regards
Michel
Re: Horizontal to Vertical Transpose. [message #398523 is a reply to message #397744] Thu, 16 April 2009 09:19 Go to previous messageGo to next message
yugi.adapala
Messages: 10
Registered: April 2009
Junior Member

SELECT decode(row_num,
1,
'jan',
2,
'feb',
3,
'mar',
4,
'apr',
5,
'may',
6,
'jun',
7,
'jul',
8,
'aug',
9,
'sep',
10,
'oct',
11,
'nov',
12,
'dec') mon,
decode(row_num,
1,
jan,
2,
feb,
3,
mar,
4,
apr,
5,
may,
6,
jun,
7,
jul,
8,
aug,
9,
sep,
10,
oct,
11,
nov,
12,
DEC) val
FROM (SELECT m.ROWID row_id, m.* FROM monthly m) mon,
(SELECT rownum row_num FROM dual CONNECT BY LEVEL <= 12);
Re: Horizontal to Vertical Transpose. [message #398546 is a reply to message #398523] Thu, 16 April 2009 10:54 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to post answer but could you please read OraFAQ Forum Guide
There is a "How to format your post?" section that shows you how to use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: how to clear sql%rowcount value
Next Topic: Extracting values from comma separated string and loop through them
Goto Forum:
  


Current Time: Sun Dec 11 05:58:43 CST 2016

Total time taken to generate the page: 0.12202 seconds