Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Crosstab query help

    When I run this query I get the results listed below.

    HTML Code:
    SELECT	sysdate rundate
          , d.p_productgroupcode prodgroup
          , f.dmdgroup
    	  , f.startdate
    	  , round(sum(f.qty)) QTY
    	  , sum(round(f.qty * d.p_systemavgcost)) AMT
    FROM stsc.fcst f, stsc.dfu d 
    WHERE f.startdate BETWEEN (sysdate-30) AND (sysdate+365)
    and d.dmdunit = f.dmdunit
    and d.dmdgroup = f.dmdgroup
    and d.loc = f.loc
    and d.p_dfulevel IN ( 211, 222 )
    and d.scen = f.scen
    and d.scen = 0
    and d.p_productgroupcode = 'AA030A'
    and d.dmdgroup = 'USA'
    group by sysdate
           , d.p_productgroupcode
           , f.dmdgroup
    	   , f.startdate

    I get data in this layout:
    Code:
    rundate                Prodgroup    Dmdgroup   Startdate      Qty    Amt
    ==========================================================================
    2/3/2006 4:09:19 Pm    Aa030a       Usa        2/1/2006       60     80                                    
    2/3/2006 4:09:19 Pm    Aa030a       Usa        3/1/2006       107    141                                   
    2/3/2006 4:09:19 Pm    Aa030a       Usa        4/1/2006       82     108                                   
    2/3/2006 4:09:19 Pm    Aa030a       Usa        5/1/2006       73     92                                    
    2/3/2006 4:09:19 Pm    Aa030a       Usa        6/1/2006       41     53                                    
    2/3/2006 4:09:19 Pm    Aa030a       Usa        7/1/2006       39     52                                    
    2/3/2006 4:09:19 Pm    Aa030a       Usa        8/1/2006       24     31                                    
    2/3/2006 4:09:19 Pm    Aa030a       Usa        9/1/2006       18     22                                    
    2/3/2006 4:09:19 Pm    Aa030a       Usa        10/1/2006      14     19                                    
    2/3/2006 4:09:19 Pm    Aa030a       Usa        11/1/2006      11     15                                    
    2/3/2006 4:09:19 Pm    Aa030a       Usa        12/1/2006      7      9                                     
    2/3/2006 4:09:19 Pm    Aa030a       Usa        1/1/2007       18     24                                    
    2/3/2006 4:09:19 Pm    Aa030a       Usa        2/1/2007       25     33
    I need the data in this layout though. I think this can be done with a crosstab query, but I'm not sure of the syntax.

    Code:
    RUNDATE                PRODGROUP    DMDGROUP   MONTH1QTY  MONTH1AMT   MONTH2QTY  MONTH2AMT  ETC...
    ==========================================================================================
    2/3/2006 4:09:19 PM    AA030A       USA        60         80          107        141

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can read abou pivot queries on my blog.

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    I don't know how to get the month (2/1/06, 3/1/06, ...) to print as column headers. Here's what I have so far. I realize this is only for a single column, but it I can get it to work, I can add the remaining columns. The values in the startdate are dynamic and will change every month.

    Code:
    SELECT	sysdate rundate
          , d.p_productgroupcode prodgroup
          , f.dmdgroup
          , MAX(DECODE(f.startdate, '<what do I put here??> ', ROUND(SUM(f.qty)))) M1QTY
    --	  , ROUND(SUM(f.qty)) QTY
    --	  , ROUND(sum(f.qty * d.p_systemavgcost)) AMT
      FROM stsc.fcst f
         , stsc.dfu d 
     WHERE f.startdate BETWEEN (sysdate-30) AND (sysdate+355)
       AND d.dmdunit = f.dmdunit
       AND d.dmdgroup = f.dmdgroup
       AND d.loc = f.loc
       AND d.p_dfulevel IN ( 211, 222 )
       AND d.scen = f.scen
       AND d.scen = 0
       AND d.p_productgroupcode = 'FB140A'
       AND d.dmdgroup = 'USA'
    GROUP BY sysdate
           , d.p_productgroupcode
           , f.dmdgroup
    Last edited by ssmith001; 02-06-06 at 13:42.

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

    MAX(DECODE(f.startdate, '<what do I put here??> ', ROUND(SUM(f.qty)))) M1QTY

    I think you should have this:

    ROUND(SUM(DECODE(TO_CHAR(f.startdate,'YYYYMM'), '200602', f.qty)))) M1QTY

  5. #5
    Join Date
    Sep 2005
    Posts
    220
    Tony, thanks for the reply. There are two issues however:

    1) when I run it, I get "ORA-00923: FROM keyword not found where expected"

    2) the other issue is that this month Month1 = 2/1/2006, next month it will be 3/1/2006, etc. Isn't the "200602" in your solution hard-coding the Month1 date?

  6. #6
    Join Date
    Sep 2005
    Posts
    220
    Tony, I figured out the first issue (1 too many paren), but I still wonder about issue 2.

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

    ROUND(SUM(DECODE(TO_CHAR(f.startdate,'YYYYMM'), TO_CHAR(ADD_MONTHS(SYSDATE-30,0),'YYYYMM'), f.qty)))) M1QTY,
    ROUND(SUM(DECODE(TO_CHAR(f.startdate,'YYYYMM'), TO_CHAR(ADD_MONTHS(SYSDATE-30,1),'YYYYMM'), f.qty)))) M2QTY,
    ...

  8. #8
    Join Date
    Sep 2005
    Posts
    220
    That did it. I can't begin to thank you enough!

    Steve

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    Do you want Month 1 2005 added to month 1 2006?
    or are those seperate months for your columns?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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