swapping Rows and columns
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: swapping Rows and columns

  1. #1
    Join Date
    Mar 2003
    Posts
    20

    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. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  3. #3
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    Can you export the data to excel and piviot the rows and columns, that would be the esiest way.

  4. #4
    Join Date
    Mar 2003
    Posts
    20
    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

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  6. #6
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    Does it has to be done by writing SQL or the tool they are using in DW env.

  7. #7
    Join Date
    Jan 2003
    Location
    Bahrain
    Posts
    109
    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
  •  



Click Here to Expand Forum to Full Width