DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Crosstab challenge !!

  1. #1
    Join Date
    Nov 2001
    Posts
    13

    Question Crosstab challenge !!

    Hi,

    Does anyone know if crosstab queries are possible in SQL. As a simple example i've got 2 tables containing Sites and Tasks.

    I'm trying to show the number of completed tasks per Region (as rows), per Phase (as columns). I can get close using the following decode statement:

    Code:
    select mysite.region, 
      decode(mysite.phase, 'Ph1', count(mytask.completed), 0) "Ph1",
      decode(mysite.phase, 'Ph2', count(mytask.completed), 0) "Ph2"
    from mysite, mytask
    where mytask.site = mysite.site
    group by mysite.region, mysite.phase;
    
    REGION	Ph1	Ph2
    ------- ------- --------
    East	1	0
    West	1	0
    North	2	0
    North	0	1
    South	2	0
    South	0	1
    The trouble is get 1 row, per region, per phase.
    Is it possible to combine these into 1 row per region as below?

    Code:
    REGION	Ph1	Ph2
    ------- ------- ----
    East	1	0
    West	1	0
    North	2	1
    South	2	1
    Any advice greatly appreciated!

    Thanks

    Ben
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    Try this:

    select mysite.region,
    SUM(decode(mysite.phase, 'Ph1', count(mytask.completed), 0)) "Ph1",
    SUM(decode(mysite.phase, 'Ph2', count(mytask.completed), 0)) "Ph2"
    from mysite, mytask
    where mytask.site = mysite.site
    group by mysite.region;

  3. #3
    Join Date
    Nov 2001
    Posts
    13
    Highlander,

    Cheers for the steer but no joy i'm afraid. ORA-00937: not a single-group group function.

    Your idea sent me on convuloted round trip that eneded up with the following solution though.

    Code:
    select mysite.region, 
      count(decode(mysite.phase, 'Ph1', mytask.completed)) "Ph1",
      count(decode(mysite.phase, 'Ph2', mytask.completed)) "Ph2"
    from mysite, mytask
    where mytask.site = mysite.site
    group by mysite.region;
    Thanks

    Ben

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