Thanks for the question, Richard.
Asked: January 06, 2014 - 11:35 pm UTC
Last updated: January 07, 2014 - 7:16 pm UTC
Version: 11.2.0.3
Viewed 10K+ times! This question is 
You Asked
Hi tom,
Thanks for submitting my question and really appreciate your response.
I have a question about SQL pivot in clause using subquery. If I used the fixed string, it works fine. However, I'd like to use subquery to make it more flexable.
When I using subquery in the pivot in clause with xml, it works. But It doesnot work without xml.
I wonder if this subquery in the pivot in clause is only support XML format. But according to Oracle SQL language Reference, it seems should support both.
Would you please confirm if it support subquery in the pivot in clause without xml? It it is, please provide an example. Thanks.
Richard
and Tom said...
the ansi SQL pivot requires a known set of columns to work on at hard parse time. A query that is hard parsed must have a known number of columns, with a known set of column names and a known set of datatypes.
In order to do a 'dynamic pivot', you need to run two queries. One to generate the set of columns you want to have in the query and the other to actually do the pivot.
see
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:5312784100346298561
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment