Results 1 to 4 of 4
  1. #1
    Join Date
    May 2010
    Posts
    56

    Dynamic subquery in Pivot Query

    Hi all,

    I have the following requirement:

    For a table XYZ I want to have the pivot query with a subquery:

    CREATE TABLE XYZ
    (
    a NUMBER NOT NULL,
    b NUMBER NOT NULL,

    f VARCHAR2(50 BYTE)

    )


    Insert into xyz
    (a,b,f)
    Values
    (1, 3807,
    'HE');
    Insert into xyz
    (a,b,f)
    Values
    (2, 3808, 'SHE');

    Insert into xyz
    (a,b,f)
    Values
    (3, 3809, 'WE');

    Insert into xyz
    (a,b,f)
    Values
    (4, 3807, 'FREE');


    Currently I have data as:

    A B F

    1 3807 HE
    2 3808 SHE
    3 3809 WE
    4 3807 FREE



    Now for the above table I need data as :

    A B 'HE' 'SHE' 'WE' 'FREE'

    1 3807 1
    3 3809 1
    4 3807 1
    2 3808 1


    i.e. pivot for values of the F.

    But I want it as a subquery or as a dynamic query for the values of column F.
    I don't have to hard code the values of column F.

    Currently I have the following query which gives the required output but here instead of the hard coded values of column F I want a subquery like Select F from XYZ.

    Current QueryTHIS WORKS ONLY IN ORACLE 11G)
    WITH pivot_data AS (
    SELECT a,b,f
    FROM XYZ
    )
    SELECT *
    FROM pivot_data
    PIVOT (
    SUM( 1) --<-- pivot_clause
    FOR f --<-- pivot_for_clause
    IN ('HE','SHE','WE','FREE') --<-- pivot_in_clause
    );

    But I want to have the values of F given dynamically.

    Please help.

    Thanks and Regards,
    Sudhir

  2. #2
    Join Date
    May 2010
    Posts
    56
    Sorry about the typo the output should be as follows with 1 in each row for each values of F acting as a column

    A B 'HE' 'SHE' 'WE' 'FREE'

    1 3807 1
    3 3809 1
    4 3807 1
    2 3808 1

    I want the same output but with dynamic generation of values of column F in table XYZ

    Regards,
    Sudhir

  3. #3
    Join Date
    May 2010
    Posts
    56
    I was expecting some reply on this.
    Please help.

  4. #4
    Join Date
    Nov 2010
    Location
    Pensacola, FL
    Posts
    5
    We have someone who needs to do something like this. Right now the plan is to create some dynamic SQL. You may find this page useful. Oracle 11g: New Pivot Table Feature | The Pythian Blog

Posting Permissions

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