sql combine rows with same date

问题:

I want to combine rows when they have same date and Item# while aggregating received QTY and Outstanding QTY. Also, PO# should be combined with “, “.

Please refer to the image or table below. Thank you in advance!!

This is my SQL Query...

SELECT * 
from [mason01].[dbo].[po_east] as t1
inner join (select distinct [Date],[ITEMNO],[PONUMBER],[LOCATION],[Received],[Outstanding]
  FROM [mason01].[dbo].[po_east] group by [Date], [ITEMNO],[PONUMBER],[LOCATION],[Received],[Outstanding]) as t2
  on t1.Date=t2.Date and t1.ITEMNO=t2.ITEMNO

Date ITEMNO PONUMBER LOCATION Received Outstanding

4/22/2018 MA1005 SON18497 SF 50 50
4/22/2018 MA1005 SON18562 SF 300 0

Date ITEMNO PONUMBER LOCATION Received Outstanding

4/22/2018 MA1005 SON18497, SON18562 SF 350 50

Refer this image:

回答1:

You can try using stuff() function

SELECT
      [Date],[ITEMNO],[LOCATION],sum([Received]) as [Received] ,sum([Outstanding]) as [Outstanding]
      ,ponum = STUFF((
          SELECT ',' + b.[PONUMBER]
          FROM [mason01].[dbo].[po_east] b
          WHERE a.[Date] = b.[Date] and a.[ITEMNO]=b.[ITEMNO]
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM [mason01].[dbo].[po_east] a
group by [Date],[ITEMNO],[LOCATION]