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

    Unanswered: 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
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: crosstab query (pivot)

    No, the number of columns in a given query is fixed. However, you can generate a new query dynamically. I have a little package that does this. It doesn't quite work for your query, as unusually you are disecting the data in 2 different ways (type and category), but it comes close.

    To generate your query I would do this:

    SQL> begin
    2 pivot.print_pivot_query
    3 ( group_cols => 'ID'
    4 , pivot_col => 'type'
    5 , tables => 'cross'
    6 , value_cols => 'area'
    7 );
    8 pivot.print_pivot_query
    9 ( group_cols => 'ID'
    10 , pivot_col => 'category'
    11 , tables => 'cross'
    12 , value_cols => 'area'
    13 );
    14 end;
    15 /
    select ID
    , sum(DECODE(type,'ARCHIVES', area)) as ARCHIVES
    , sum(DECODE(type,'OFFICE', area)) as OFFICE
    from cross
    group by ID
    order by ID
    select ID
    , sum(DECODE(category,'PLAN', area)) as PLAN
    , sum(DECODE(category,'SPACE', area)) as SPACE
    from cross
    group by ID
    order by ID

    PL/SQL procedure successfully completed.

    Now in this case I'd need to edit the output to remove these lines from the middle:

    from cross
    group by ID
    order by ID
    select ID

    to get:

    select ID
    , sum(DECODE(type,'ARCHIVES', area)) as ARCHIVES
    , sum(DECODE(type,'OFFICE', area)) as OFFICE
    , sum(DECODE(category,'PLAN', area)) as PLAN
    , sum(DECODE(category,'SPACE', area)) as SPACE
    from cross
    group by ID
    order by ID

    Whenever you need to redefine the query, you would just re-run the PL/SQL above.

    I have attached the package code in case it is of any use to you.
    Attached Files Attached Files
    Last edited by andrewst; 01-27-04 at 08:52.

  3. #3
    Join Date
    Jan 2004
    Posts
    7

    Re: crosstab query (pivot)

    Hi,

    I saw a reply from you regarding a crosstab query where you had attached a package called pivot2.sql .I have a similar requirement and would like to use that package.But i don't understand the PARSE.varchar2_table and PARSE.delimstring_to_table. Is there any other piece to this code that is missing.If i compile this package as it is, it asks me to define the above.Please help ASAP.




    Originally posted by 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

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: crosstab query (pivot)

    Sorry, I forgot that my Pivot package had a dependency on my Parse package! Here it is...
    Attached Files Attached Files

  5. #5
    Join Date
    Jan 2004
    Posts
    7

    Re: crosstab query (pivot)

    Hi Raf,
    Thanks a lot for u'r timely help...!

    Originally posted by 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

Posting Permissions

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