Home » SQL & PL/SQL » SQL & PL/SQL » Help needed
Help needed [message #197779] Thu, 12 October 2006 12:30 Go to next message
maxboom123
Messages: 9
Registered: August 2006
Junior Member
Hi All,
I am new to PL/SQL n need a small help.I have a tabe containing columns P ,A1,A2,A3,A4,A5
and the data in the table is as follows

P A1 A2 A3 A4 A5
- -- -- -- -- --
10 0 5 0 4 0
11 0 0 0 0 0
12 4 3 1 0 0
13 0 0 0 0 0
14 1 2 1 4 5

I want create a new table/view/query which interchange the columns into rows.that is ,it should fetches the data in the following manner

P COL_NAME COL_VALUE
-- --- ----
10 A1 0
10 A2 5
10 A3 0
10 A4 4
10 A5 0
11 A1 0
11 A2 0
11 A3 0
11 A4 0
11 A5 0
. . . . . . . . .. ..
. . . . . . . . . . .
Here COL_NAME,COL_VALUE are the names of the columns in the new table/view.

Can some one please help me to do this?

Regards,
Mx
Re: Help needed [message #197786 is a reply to message #197779] Thu, 12 October 2006 12:50 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
select p, decode(r, 1, 'A1', 2, 'A2', 3, 'A3', 4, 'A4', 5, 'A5') COL_NAME,
	  decode(r, 1, a1, 2, a2, 3, a3, 4, a4, 5, a5) COL_VALUE
	from tab1, (select rownum r from all_objects where rownum <= 5)
order by 1, 2;

[Updated on: Thu, 12 October 2006 12:52]

Report message to a moderator

Previous Topic: sql query
Next Topic: SQL AGGREGATE AND FORMAT QUESTION
Goto Forum:
  


Current Time: Sat Dec 03 09:46:23 CST 2016

Total time taken to generate the page: 0.22452 seconds