crosstab query (pivot)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: crosstab query (pivot)

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    crosstab query (pivot)

    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

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483
    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

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483
    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
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width