5 Replies Latest reply on Aug 4, 2017 12:58 PM by Frank Kulash

    Dynamic set in Pivot IN clause - Query

    Shilpi Pauranik-Oracle

      My concern here is if we have a dynamic set of column names that need to be pivoted, how can we achieve it. I have read that PIVOT supports subquery but never worked for me.

       

      PIVOT syntax-

      SELECT * FROM

      (

        SELECT column1, column2

        FROM tables

        WHERE conditions

      )

      PIVOT

      (

        aggregate_function(column2)

        FOR column2

        IN ( expr1, expr2, ... expr_n) | subquery

      )

      ORDER BY expression [ ASC | DESC ];

       

      In the IN clause it is mentioned that we can use subquery but it didn't worked. It will great if anyone can help/provide any example which is using subquery instead of static values.

       

      Thanks,

      Shilpi