Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: problem with a query

    Hi,
    I've this table:

    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'd like to create a query to get this output:

    ID OFFICE SERVICE SPACE PLAN ARCHIVES MEETING
    01 50 0 100 0 0 0
    02 0 0 110 10 100 20
    03 0 20 100 20 80 20

    50 is sum(area) of ID=01 FOR TYPE=OFFICE.............

    This is a crosstab query, I don't know if Oracle Can create a crosstab query, I hope so.

    Could you help me??
    Thanks
    Raf

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Oracle can if you have a fixed number of columns. You can do it in a number of ways but the way I usually do it is as follows.


    select
    ID,
    sum(decode(type,'OFFICE',area,null)) office,
    sum(decode(type,'ARCHIVES',area,null)) archives,
    sum(decode(category,'SPACE',area,null)) space,
    sum(decode(category,'PLAN',area,null)) plan,
    ...
    from <table>
    group by id

    You could use case instead of decode if you have 8i or better.

    Alan

Posting Permissions

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