1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to display row values as multiple Column and Row values?

Discussion in 'SQL PL/SQL' started by ygsunilkumar, May 5, 2010.

  1. ygsunilkumar

    ygsunilkumar Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    Please find the Test Case below.
    Code (SQL):

    --Creation of Table
    CREATE TABLE tb1
    (ID NUMBER(4),
    event varchar2(20),
    vdate DATE);
     
    Code (SQL):

    --Inserting Values into the Table.
    INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
       INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
       INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
       INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
       INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')
       INTO tb1 (ID, event, vdate) VALUES (02, 'V1', '01-JAN-2010')
       INTO tb1 (ID, event, vdate) VALUES (02, 'V2', '02-FEB-2010')
       INTO tb1 (ID, event, vdate) VALUES (02, 'V3', '04-MAR-2010')
       INTO tb1 (ID, event, vdate) VALUES (02, 'V4', '03-APR-2010')
       INTO tb1 (ID, event, vdate) VALUES (02, 'V5', '05-MAY-2010')
    SELECT * FROM dual;
     
    Code (SQL):

    --Selecting data from Table.
    SELECT * FROM TB1;

            ID EVENT                VDATE
    ---------- -------------------- ---------
             1 V1                   01-JAN-09
             1 V2                   02-FEB-09
             1 V3                   04-MAR-09
             1 V4                   03-APR-09
             1 V5                   05-MAY-09
             2 V1                   01-JAN-10
             2 V2                   02-FEB-10
             2 V3                   04-MAR-10
             2 V4                   03-APR-10
             2 V5                   05-MAY-10

    10 ROWS selected.
     

    Please help how can i display the data as below format using Oracle 9i SQL. Thanks in Advance
    Code (SQL):

    ID      V1         V2        V3         V4              V5
    1   1-Jan-09    2-Feb-09    4-Mar-09    3-Apr-09    5-May-09
    2   1-Jan-10    2-Feb-10    4-Mar-10    3-Apr-10    5-May-10
     
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India