Results 1 to 3 of 3

Thread: How do I transpose Rows into Columns?

  1. #1
    Junior Member
    Join Date
    Mar 2007
    Answers
    2

    How do I transpose Rows into Columns?

    How do I transpose Rows into Columns?

    For example, if I've the data as follows:

    Name Value
    ==== ====
    Name1 V11
    Name1 V12
    Name1 V13
    Name2 V21
    Name2 V22

    I want the OUTPUT to be as follows:

    Name1 V11 V12 V13
    Name2 V21 V22

    Apperciate if anyone can help me out in this.

    Waiting for your reply,

    Cheers,


  2. #2
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

    Re: How do I transpose Rows into Columns?

    hi i did it with my table as yr req. try this in yr table----

    (it's very easy)--


    10:27:36 SQL> seelct *from test;
    SP2-0734: unknown command beginning "seelct *fr..." - rest of line ignored.
    10:47:40 SQL> select *from test;

    C
    -
    a
    a
    a
    a
    a
    b
    b
    b

    8 rows selected.

    Elapsed: 00:00:00.00

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=16)
    1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=3 Card=8 Bytes
    =16)




    10:47:48 SQL> select
    10:47:57 2 max(decode(rn, 1, col)) col1,
    10:47:57 3 max(decode(rn, 2, col)) col2,
    10:47:57 4 max(decode(rn, 3, col)) col3,
    10:47:57 5 max(decode(rn, 4, col)) col4,
    10:47:57 6 max(decode(rn, 5, col)) col5
    10:47:57 7 from (
    10:47:57 8 select col, row_number() over (partition by col order by col) rn
    10:47:57 9 from test
    10:47:57 10 order by col)
    10:47:57 11 group by col;

    C C C C C
    - - - - -
    a a a a a
    b b b

    Elapsed: 00:00:00.00

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=30)
    1 0 SORT (GROUP BY NOSORT) (Cost=3 Card=2 Bytes=30)
    2 1 VIEW (Cost=3 Card=8 Bytes=120)
    3 2 WINDOW (SORT) (Cost=3 Card=8 Bytes=16)
    4 3 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=3 Card=8
    Bytes=16)


  3. #3
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: How do I transpose Rows into Columns?

    try writing some matrix query using decode as shown in sample of previous example.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact