CODEWITHSUNDEEP
oracle-databaseoracle11g
Vijay Krish
Vijay Krish

Reputation: 297

Alternate method for PIVOT - IN with sub-query support

As per oracle documentation, PIVOT function doesn't support sub-query in the "IN" clause but it is possible in the PIVOT XML function. E.g.

SELECT *
FROM table_name
PIVOT
(
  SUM(column_name_1)
  FOR [column_name_2] IN (['Output_Column1'],['Output_Column2'])
)
AS aliasName

I need to replace the ['Output_Column1'],['Output_Column2'] with sub-query.

Is there some other function equivalent to PIVOT where we can supply sub-query instead hard coding the entire output columns or even in the PIVOT function itself?.

Upvotes: 5

Views: 1893

Answers (1)

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

No, the number of columns must be known at parse time. For PIVOT XML there is no problem because such query returns only one column.

Upvotes: 1

Related Questions