swapping Rows and columns

Hi Everybody,
I have a table with column as shown below...
|col1|col2|col3|col4|
----|----|----|----|----|-----
row1| A1 |A2 | A3 | A4 |
----|----|----|----|----|-----
row2| B1 | B2 | B3 | B4 |
----|----|----|----|----|----- fig 1
row3| C1 | C2 | C3 | C4 |
----|----|----|----|----|-----
row4| D1 | D2 | D3 | D4 |
----|----|----|----|----|------
I Have to show the result as shown below.
I need a query to show the result of a table.
|row1|row12|row3|row4|
----|----|----|-----|-----|----- Fig 2
col1| A1 | B1 | C1 | D1 |
----|----|----|---- |-----|-----
col2| A2 | B2 | C2 | D2 |
----|----|----|-----|-----|-----
col3| A3 | B3 | C3 | D3 |
----|----|----|-----|-----|-----
col4| A4 | B4 | C4 | D4 |
----|----|----|-----|-----|-----
Any solution?
Suresh.H.T

2 forloops will fetch u result u wanted ( i looping for the no or records & other no of columns ).

Can you export the data to excel and piviot the rows and columns, that would be the esiest way.

Hi,
This has to be done in oracle only.
This is one of the questions asked in WIPRO interview for Datawarehousing positions.
Suresh.H.T

Quote:

---

Originally posted by sureshht
Hi,
This has to be done in oracle only.
Suresh.H.T

---

Answer wud be YES.

Quote:

---

This is one of the questions asked in WIPRO interview for Datawarehousing positions.

---

Ahh...planning for change

Does it has to be done by writing SQL or the tool they are using in DW env.

Hi suresh,

sql> select * from test;

COL1 COL2 COL3 COL4
---------- ---------- ---------- ----------
a1 a2 a3 a4
b1 b2 b3 b4
c1 c2 c3 c4
d1 d2 d3 d4

a.sql
*****

declare

cursor a is select * from user_tab_columns where table_name='TEST';
cursor b is select * from test;
aa varchar2(5000);

begin


for i in a loop
aa:=aa||i.column_name;
for ii in b loop
if i.column_id=1 then
aa:=aa||' | ' || ii.col1;
elsif i.column_id=2 then
aa:=aa||' | ' || ii.col2;
elsif i.column_id=3 then
aa:=aa||' | ' || ii.col3;
elsif i.column_id=4 then
aa:=aa||' | ' || ii.col4;
end if;
end loop;
dbms_output.put_line(aa);
aa:=' ';
end loop;
end;

sql>@a

COL1 | a1 | b1 | c1 | d1
COL2 | a2 | b2 | c2 | d2
COL3 | a3 | b3 | c3 | d3
COL4 | a4 | b4 | c4 | d4

PL/SQL procedure successfully completed.


Seelan