-
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 ).
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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
-
Originally posted by sureshht
Hi,
This has to be done in oracle only.
Suresh.H.T
Answer wud be YES.
This is one of the questions asked in WIPRO interview for Datawarehousing positions.
Ahh...planning for change
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|