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!

Sql pivot and unpivot

Discussion in 'SQL PL/SQL' started by Nikita Kataria, Oct 2, 2014.

  1. Nikita Kataria

    Nikita Kataria Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Mumbai
    Hello All,
    I am a newbie here n I am stuck up in a query. I have a table v_summary as

    Ono | UACHECK | UADESC | AU11 | AU12 | BD10 |
    ------------------------------------------------------------------------------------
    1 | 5.1 | VENDOR | 0 | 0 | 0 |
    2 | 5.2A | CUSTOMER | 0 | 0 | 0 |

    and i need to display it as

    Ono | Plant | 5.1 - VENDOR | 5.2A - CUSTOMER
    -----------------------------------------------------------------------
    1 | AU11 | 0 | 0
    2 | AU12 | 0 | 0
    3 | BD10 | 0 | 0

    i.e my columns AU11,AU12 AND BD10 should become the rows of plant columns
    and each value of uacheck concat with uadesc should become a column.

    I need it urgently. Waiting for a sooner reply. THank you all :)
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO



    If this is that 'urgent' you need to find someone local to assist you; this is a help forum, not Oracle product support, and the volunteers here do their best to help but they are not obliged to jump when you want them to.


    Sergey provided good information so please read it.
     
  4. Nikita Kataria

    Nikita Kataria Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Mumbai
    I tried every possible way before posting it into blog.i read both the docs and came to my query as

    WITH T1 AS (SELECT *
    FROM vw_summary UNPIVOT (plantvalue
    FOR plant
    IN (AU11, AU12, BD10))),
    T2
    AS (SELECT UACHECK,
    UADESC,
    PLANT,
    PLANTVALUE,
    ROW_NUMBER () OVER (PARTITION BY UADESC ORDER BY UADESC)
    AS NUM
    FROM T1)
    SELECT *
    FROM t2 PIVOT (MIN (PLANTVALUE)
    FOR (UADESC, UACHECK)
    IN ( ('VENDOR', '5.1') AS "5.1 - VENDOR",
    ('CUST', '5.2A') AS "5.2A - CUST"));

    but its not working.
     
  5. Nikita Kataria

    Nikita Kataria Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Mumbai
    don't judge anyone..i read many solutions..came up with a query i posted below..but it's not working..You can talk calmly I guess..
     
  6. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Get snippy w/ the moderator at your own risk!

    That's it's not working is self evident...can you provide how you know it's not working? Error message perhaps? The table structures would be nice.

    Also, have you thought about creating a view so you can simplify your WITH clause? I find the statement itself utterly confusing.

    CJ
     
  7. Nikita Kataria

    Nikita Kataria Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Mumbai
    CJ..I read the docs for unpivoting and pivoting..I came up with my query..but it wasn't working..I posted the code which i wrote..
    I am getting the ORA-00907: missing right parenthesis.
    but if you will check it..there isn't any..
    and about the table structure..its same as in the query i post with the mentioned columns and data..at last when no solution was working..I expected some help from technical experts who can guide us..
    maybe its a silly doubt..but every professional is once a beginner..
     
  8. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Do the individual statements that make up the entire query run w/o error?
     
  9. Nikita Kataria

    Nikita Kataria Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Mumbai
    the code is working fine on sql toad editor but when i try to run it on sqldeveloper tool..it gives me error as

    ORA-00907: missing right parenthesis
    00907. 00000 - "missing right parenthesis"
    *Cause:
    *Action:
    Error at Line: 2 Column: 38
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Then its an issue with SQL Developer and how you have it pasted into the tool, I expect. It may also be an issue with the version of SQL Developer you are using; valid SQL constructs in current releases can generate such errors in previous versions. Try upgrading and see if the error goes away.
     
    Nikita Kataria likes this.
  11. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    I figured the issue closed. That it doesn't work via SQL Dev as opposed to the TOAD editor is an entirely different issue.

    CJ
     
  12. Nikita Kataria

    Nikita Kataria Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Mumbai
    Thank you sir..:)