Forum Stats

  • 3,852,779 Users
  • 2,264,136 Discussions
  • 7,905,138 Comments

Discussions

Ambiguity in the usage of select statement in "PIVOT" clause - PART I

Vysakh Suresh - 3035408
Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
edited Dec 22, 2015 2:25AM in SQL & PL/SQL

Dear All,

Kindly advise why is it that we cant use select statement inside the PIVOT phrase.

ie in the portion highlighted in bold below,

select * from(

)

PIVOT

(

Aggregate_Function<column_name_1> FOR <column_name_2> IN (select <column_name_temp> from <table_name>)

);


NB : Discussion example query created under

Thanks and Regards,

Vysakh Suresh

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,490 Red Diamond
    edited Dec 21, 2015 8:48AM

    Hi, Vysakh,

    Vysakh Suresh - 3035408 wrote:
    
    Dear All,
    
    Kindly advise why is it that we cant use select statement inside the PIVOT phrase.
    
    ie in the portion highlighted in bold below,
    
    select * from(
    )
    PIVOT
    (
    Aggregate_Function<column_name_1> FOR <column_name_2> IN (select <column_name_temp> from <table_name>)
    );
    
    Apologies for not having an example to illustrate.. a fruitful kind advise will do..
    
    Thanks and Regards,
    Vysakh Suresh
    
    

    You can use a sub-query there.  See the SQL Language manual:

    https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#sthref7307

    for details and an example.

    Here's the reason why you can only use a sub-query if you are getting XML output.  In a SQL query, the number of columns and their names and aliases must be given to the compiler before it can do its job.  Only after the statement is compiled can any data be fetched, so the number of columns, or their names, can't depend on the data fetched.  XML output only produces 1 column, and it only has 1 (hard-coded) name.  Some other application may render that as multiple columns with dynamic names, but SQL is only producing 1 column with a fixed name.

    Vysakh Suresh - 3035408
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited Dec 21, 2015 8:44AM

    The Projection of a SELECT statement cannot be dynamic. Should be defined statically and should be available during parsing. What you are trying to accomplish is called Dynamic Pivot. There is no straight forward way to do it. But you can search this forum for this as it has been asked a lot.

    Vysakh Suresh - 3035408
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Dec 21, 2015 8:50AM
  • Vysakh Suresh - 3035408
    Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
    edited Dec 22, 2015 2:20AM

    Dear Frank, Karthik and Etbin,

    Thank you very much for the advise..

    But would you please suggest a solution for the below query,

    Thanks and Regards,

    Vysakh Suresh

This discussion has been closed.