sql合并具有相同日期的行 [英] sql combine rows with same date

问题描述

我想合并行号和日期相同的行，同时汇总收到的数量和未完成的数量。另外，PO＃应与，结合使用。


请参考下面的图片或表格。
预先谢谢您！

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!!

这是我的SQL查询...

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

日期ITEMNO PONUMBER位置已收到

Date ITEMNO PONUMBER LOCATION Received Outstanding

2018/4/22 MA1005 SON18497 SF 50 50

2018年4月22日MA1005 SON18562 SF 300 0

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

日期ITEMNO PONUMBER位置已收到

Date ITEMNO PONUMBER LOCATION Received Outstanding

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

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

引用此图片：

推荐答案

您可以尝试使用（）函数

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]

这篇关于sql合并具有相同日期的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！