crosstab query (pivot)

[raf]

Hi,
I've this table CROSS:

ID AREA TYPE CATEGORY
01 20 OFFICE SPACE
01 30 OFFICE SPACE
01 50 ARCHIVES SPACE
02 10 MEETING SPACE
02 10 MEETING SPACE
02 100 ARCHIVES SPACE
03 20 SERVICE PLAN
03 20 MEETING SPACE
03 80 ARCHIVES SPACE



I created this query for get a crosstab (rows become columns):

select
ID,
sum(decode(type,'OFFICE',area,0)) office,
sum(decode(type,'ARCHIVES',area,0)) archives,
sum(decode(category,'SPACE',area,0)) space,
sum(decode(category,'PLAN',area,0)) plan
from CROSS
group by id

It's correct but now my problem is this:

If Type or Category changes name (for example OFFICE become HOME), my crosstab is incorrect.

Is it possibile create a dynamic query that change automatically type or category columns??

Thanks in advance!
Raf

[LKBrwn_DBA]

You could try dynamic SQL like this:

set pages 0 head off recsep off feed off term off
spo cross.sql
select 'select ID' from dual
union
select ',sum(decode(type,'||type||',area,0)) '||type
from (select distinct type from CROSS)
union
select ',sum(decode(category,'||category||',area,0)) '||category
from (select distinct category from CROSS)
union
select 'from CROSS group by id;' from dual
/
spo off
set pages 55 head on recsep on feed on term on
@cross.sql

[LKBrwn_DBA]

You may need to add some single quotes and a sequence to preserve the order:

set pages 0 head off recsep off lin 132 feed off term off
col sq noprint
spo cross.sql
select 0000 sq,'select ID' from dual
union
select rownum sq,',sum(decode(type,'''||type||''',area,0)) '||type
from (select distinct type from CROSS)
union
select rownum+1000 sq,',sum(decode(category,'''||category||''',area,0)) '||category
from (select distinct category from CROSS)
union
select 9999 sq,'from CROSS group by id;' from dual
order by 1
/
spo off
set pages 55 head on recsep on lin 80 feed on term on
@cross.sql