SQL needs modification

From: Joe <RappaJ_at_nycha.nyc.gov>
Date: 23 Feb 2004 14:03:34 -0800
Message-ID: <ab8e8633.0402231403.7038d66a@posting.google.com>

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

CURRENTLY: 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,1,0)) "1998",
                SUM(DECODE(muh.YEAR,1999,1,0)) "1999",
                 SUM(DECODE(muh.YEAR,2000,1,0)) "2000",
                sum(muh.TRANS_QTY) usage
            from MATERIAL_USAGE_HISTORY muh
                where   muh.item_number  = '0101973572'
                and muh.month  = 01
                and (muh.year between 1998 and 2000)
             group by     muh.MONTH,muh.YEAR

MONTH 1998 1999 2000 USAGE
------ ---- ---- ----- ------

JAN       1    0     0     17
JAN       0    1     0      2
JAN       0    0     1      2

************************

This is just a short version of my query. It uses parameters. I would like to have the report displaying total_usage of 17 in 1998, 2 in 1999 and 2 in 2000 on one line.
Can I somehow use the decode function to give me the sum(trans_qty) for 1998's, 1999's, etc...?

month and year are both numeric columns.

Thanks Received on Mon Feb 23 2004 - 16:03:34 CST