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

Vysakh Suresh - 3035408

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

Frank Kulash

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.

Karthick2003

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.

Etbin

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#CHDCEJJE

specific info required.

Regards

Etbin

Vysakh Suresh - 3035408

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