Cross Tab query using DECODE

[cantona]

I have produced a cross tab style query using the following SQL;



SELECT uio.owning_organisation,

COUNT(DECODE(pu.progress_code,'AA',pu.person_code,NULL)) AA,

COUNT(DECODE(pu.progress_code,'AACK',pu.person_code,NULL)) AACK,

COUNT(DECODE(pu.progress_code,'SCH',pu.person_code,NULL)) SCH

FROM

people_units pu,

unit_instance_occurrences uio

WHERE

pu.uio_id = uio.uio_id AND

pu.unit_type = 'A' AND

pu.calocc_code = '0708'

GROUP BY

uio.owning_organisation

ORDER BY

uio.owning_organisation



This works perfectly well, but ive had to hard code the values of progress_code into the query. These may be added to. If new codes are added to the system I will have to manually update this query each time. Is there a way of producing a cross tab style output where you dont necessarily know all the possible column values?

 

[Dagon]

The problem is that you don't know the number of columns in advance, so the only option would be to use some sort of dynamic SQL. You could maybe have a driving table that contains the columns you want to aggregate and then generate the query using a "SQL from SQL" technique.

 

[cantona]

Hi Dagon,

Thought that might be the case! nothings ever straight forward!! do you know of any good tutorials / online resources on this particular area? Its something that i'll probably need to use over and over again so worth the time investigating. Thought it would be quite a common thing for people to want to build a cross tab style query when the columns are not necessarily known. Havent managed to find anything so far.