I'm using pivot in Oracle PL SQL Developer as follows:
SELECT * FROM population PIVOT (AVG(Total) for Data_Type IN ('Group1','Group2','Group3'))
This works fine, but I don't want to have to edit every time a new column is added or one is changed (i.e. Group4, 5, 6 etc), so I tried a sub-query as follows:
SELECT * FROM population PIVOT (AVG(Total) for Data_Type IN (SELECT Data_Type FROM population))
This results in the following error: ORA-00936: missing expression.
After some research, it appears that I can generate the results with XML, so I tried the following:
SELECT * FROM population PIVOT XML(AVG(Total) for Data_Type IN (ANY))
This actually generates the desired data, but in XML format. So my question is, how can I convert the XML results into standard table format within PL SQL Developer? Or, if I want to bring the generated XML file into a tool like Crystal Reports, I need to have a schema file for these results. Is that something that can easily be auto generated within the SQL?