Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    1

    Question Unanswered: convert row into column ..help plz..

    i have following situation

    workshop

    shop coach_Number
    ICS 1467
    ICS 1468
    ICS 1237
    CRS 3455
    CRS 9654
    ABS 4656
    PAS 5889

    and i want out put as below

    ICS CRS ABS PAS
    1467 3455 4656 5889
    1468 9654 null null
    1237 null null null

    can anyone help me?
    thanx in advance..

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What is your version. 11G can do it with a native command and there are various solutions for lower levels. Search on the keyword PIVOT in this forum
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Sep 2004
    Posts
    15
    Not exactly what you are looking for... but based on the info you provided, you can do something lke the following to get what you want


    create table test2 (shop varchar2(5), coach_number number);

    insert into test2 values ('ICS', 1467);
    insert into test2 values ('ICS', 1468);
    insert into test2 values ('ICS', 1237);
    insert into test2 values ('CRS', 3455);
    insert into test2 values ('CRS', 9654);
    insert into test2 values ('ABS', 4656);
    insert into test2 values ('PAS', 5889);

    select
    decode(shop, 'ICS', coach_number, null) ICS,
    decode(shop, 'CRS', coach_number, null) CRS,
    decode(shop, 'ABS', coach_number, null) ABS,
    decode(shop, 'PAS', coach_number, null) PAS
    from test2;


    I know... there are extra rows and the rows are not nicely tucked up, but this is just an example of how to achive what you are looking for

  4. #4
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •