Skip to Content
0
Former Member
Jan 11, 2008 at 04:02 AM

How to Group Few Rows into One Rows In Query

20 Views

Dear All,

I've use the "Group By" syntax in my query, but when the result display, it still show few rows instead of one row.

Current Results

----


item Jan Feb Mar

------ -


-
-

A 10 0 20

A 10 0 0

A 0 5 0

B 0 0 10

Desire Result

----


item Jan Feb Mar

------ -


-
-

A 20 5 20

B 0 0 10

My query is:

----


/*select from [dbo].[oinv] T0 */

declare @customer varchar (200)

/* where */

set @customer = /* T0.Cardname */ '[%A]'

/*select from [dbo].[inv1] T1 */

declare @fromdate as datetime

/* where */

set @fromdate = /* T1.DOCDATE */ '[%1]'

/*select from [dbo].[inv1] T2 */

declare @tilldate as datetime

/* where */

set @tilldate = /* T1.DOCDATE */ '[%2]'

SELECT inv1.ITEMCODE, oitm.itemname,

CASE WHEN MONTH(INV1.DOCDATE) = 1 THEN SUM(QUANTITY) ELSE NULL END AS 'JAN',

CASE WHEN MONTH(INV1.DOCDATE) = 2 THEN SUM(QUANTITY) ELSE NULL END AS 'FEB',

CASE WHEN MONTH(INV1.DOCDATE) = 3 THEN SUM(QUANTITY) ELSE NULL END AS 'MAR',

CASE WHEN MONTH(INV1.DOCDATE) = 4 THEN SUM(QUANTITY) ELSE NULL END AS 'APR',

CASE WHEN MONTH(INV1.DOCDATE) = 5 THEN SUM(QUANTITY) ELSE NULL END AS 'MAY',

CASE WHEN MONTH(INV1.DOCDATE) = 6 THEN SUM(QUANTITY) ELSE NULL END AS 'JUN',

CASE WHEN MONTH(INV1.DOCDATE) = 7 THEN SUM(QUANTITY) ELSE NULL END AS 'JUL',

CASE WHEN MONTH(INV1.DOCDATE) = 8 THEN SUM(QUANTITY) ELSE NULL END AS 'AUG',

CASE WHEN MONTH(INV1.DOCDATE) = 9 THEN SUM(QUANTITY) ELSE NULL END AS 'SEPT',

CASE WHEN MONTH(INV1.DOCDATE) = 10 THEN SUM(QUANTITY) ELSE NULL END AS 'OCT',

CASE WHEN MONTH(INV1.DOCDATE) = 11 THEN SUM(QUANTITY) ELSE NULL END AS 'NOV',

CASE WHEN MONTH(INV1.DOCDATE) = 12 THEN SUM(QUANTITY) ELSE NULL END AS 'DEC'

FROM OINV inner join inv1 on oinv.DocEntry = inv1.DocEntry INNER JOIN OITM on inv1.itemcode = oitm.itemcode

WHERE oinv.cardname =@customer AND inv1.DOCDATE >=@fromdate AND inv1.DOCDATE <=@tilldate

GROUP BY inv1.ITEMCODE, oitm.itemname, inv1.docdate

I've tried to write in "Select Syntax" too, but still get the same result. I'm really have no idea what's wrong with my query. Thanks for all your help!

Cheers,

Serene