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!

Problem on Pivot Table on oracle 11g

Discussion in 'SQL PL/SQL' started by Guang Ming, Mar 4, 2013.

  1. Guang Ming

    Guang Ming Active Member

    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Jakarta
    Could you please help me to figure out the pivot table? here is the first table :

    Date 1 2 3 4 5
    -----------------------------------------
    20130101 0.12 0.13 0.43 0.32 0.22
    20130102 0.22 0.31 0.13 0.31 0.29
    20130103 0.32 0.12 0.33 0.12 0.34

    I want this table to be like this :

    Date Number Values
    ---------------------------
    20130101 1 0.12
    20130101 2 0.13
    20130101 3 0.43
    20130101 4 0.32
    20130102 5 0.22
    20130102 1 0.22
    20130102 2 0.31
    20130102 3 0.13
    20130102 4 0.31
    20130102 5 0.29
    20130103 1 0.32
    20130103 2 0.12
    20130103 3 0.33
    20130103 4 0.12
    20130103 5 0.34

    I've tried to find the specific query for this like using "decode", but it didn't work for me.

    here is a website that I've tried : Advice Using Pivot Table in Oracle.

    Could you please help me to figure this out? Thank you so much for your help.
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi ,
    Here is the required output format.

    Table Creation:
    Code (Text):

    SQL> CREATE
            TABLE
            PIVOT_EXAMPLE(DATE1 NUMBER,"1" NUMBER,"2" NUMBER,"3" NUMBER,"4" NUMBER, "5" NUMBER);
     
    Inserting records into table:
    Code (Text):

    SQL> INSERT INTO
                  PIVOT_EXAMPLE
                     VALUES(20130103  ,0.32 ,0.12 ,0.33 ,0.12 ,0.34);

    SQL> INSERT INTO
                  PIVOT_EXAMPLE
                     VALUES(20130102  ,0.22 ,0.11 ,0.42 ,0.19 ,0.61);

     
    Normal Record Query:
    Code (Text):

    SQL> SELECT * FROM PIVOT_EXAMPLE;

    Output:
    Date1        1     2     3      4     5
    20130102  0.22   0.11  0.42 0.19   0.61
    20130103  0.32   0.12  0.33 0.12   0.34

     
    Required Output Format:
    Code (Text):

    SQL> SELECT DATE1,COL, VALUE
                 FROM
                   (SELECT CAST("1" AS VARCHAR2(10)) AS "1", CAST("2" AS VARCHAR2(10)) AS "2",
                            CAST("3" AS VARCHAR2(10)) AS "3", CAST("4" AS VARCHAR2(10)) AS "4", CAST("5" AS VARCHAR2(10)) AS "5",
                            CAST(DATE1 AS VARCHAR2(100)) AS DATE1 FROM PIVOT_EXAMPLE) P
                  UNPIVOT
                   (VALUE FOR COL IN
                        ("1", "2", "3", "4","5")
                   ) UNPVT;

    Output:
    DATE1         COL     VALUE
    20130102    1   .22
    20130102    2   .11
    20130102    3   .42
    20130102    4   .19
    20130102    5   .61
    20130103    1   .32
    20130103    2   .12
    20130103    3   .33
    20130103    4   .12
    20130103    5   .34

     
     
  3. Guang Ming

    Guang Ming Active Member

    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Jakarta
    Hi Bharat G...

    Awesome... You helped me a lot... Thank a lot man....
     
    Bharat likes this.