Re: SQL needs modification

From: Joe <RappaJ_at_nycha.nyc.gov>
Date: 24 Feb 2004 06:20:14 -0800
Message-ID: <ab8e8633.0402240620.46df09f7@posting.google.com>

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1077580138.110102_at_yasure>...
> Joe wrote:
>
> > Hi,
> > Is there a way I can alter my query to flatten out my report to look
> > like this?:
> >
> > MONTH 1998 1999 2000
> > ------ ---- ---- ----
> > JAN 17 2 2
> >
> from
> >
> > MONTH 1998 1999 2000 USAGE
> > ------ ---- ---- ----- ------
> > JAN 1 0 0 17
> > JAN 0 1 0 2
> > JAN 0 0 1 2
> >
> > ************************
>
> SELECT month, SUM(x), SUM(y), SUM(z)
> FROM (
> <your SQL statement here>)
> GROUP BY month;

---

Thanks Dan,

Someone else had actually had a great solution for me also. Remove the year in GROUP BY, and put the sum of trans_qty in place of the value 1 in the decode stmt.

New Query:

SELECT SUBSTR (DECODE (muh.month,

                                 1, 'JAN', 2, 'FEB', 3, 'MAR', 4, 'APR', 5, 'MAY', 6, 'JUN',
                                 7, 'JUL', 8, 'AUG', 9, 'SEP', 10, 'OCT', 11, 'NOV', 12, 'DEC',

    			     month),
    		     1, 6) month,
    	     SUM (DECODE (muh.year, 1998, trans_qty, 0)) "1998",
    	     SUM (DECODE (muh.year, 1999, trans_qty, 0)) "1999",
    	     SUM (DECODE (muh.year, 2000, trans_qty, 0)) "2000"

    FROM material_usage_history muh

   WHERE   muh.item_number = '0101973572'
   AND     muh.month	= 01
   AND     muh.year BETWEEN 1998 AND 2004

   GROUP BY muh.month;

Thanks,
Joe Received on Tue Feb 24 2004 - 08:20:14 CST