SQL Server 在 select 语句中使用聚合函数连接记录

Question

我有一个 View vw_XC_DocInfo_1，其中包含 VId、VName、DocId、Amount、INum 列。下面是表格数据。

Vid   VName  DocId  Amount  INum
1     ABC     10     100    INV1
1     ABC     11     10     INV2
1     ABC     12     20     INV3
1     ABC     13     30     INV4
2     XYZ     14    200     INV5
2     XYZ     15     10     INV6
2     XYZ     16     20     INV7
2     XYZ     17     30     INV8

我需要像下面这样显示输出。

Vid   VName  DocIdsList  Amount  INumList
1     ABC    10,11       110     INV1,INV2
1     ABC    12,13       50      INV3,INV4
2     XYZ    14,15       210     INV5,INV6
3     XYZ    16,17       50      INV7,INV8

我尝试了不同的方法，但无法将聚合函数包含在 STUFF 函数中，请查找我已经厌烦的查询。

with CTE
as (
    select top 20 V.VendorId,
        V.VendorName,
        STUFF((
                select top 3 ',' + CONVERT(varchar(MAX), V1.DocumentId)
                from vw_XC_DocInfo_1 V1
                where V1.VendorID = V.VendorId
                order by V1.DocumentId
                for xml PATH('')
                ), 1, 1, '') as DocIdsList,
        STUFF((
                select top 3 ',' + CONVERT(varchar(MAX), V1.InvoiceNumber)
                from vw_XC_DocInfo_1 V1
                where V1.VendorID = V.VendorId
                order by V1.InvoiceNumber
                for xml PATH('')
                ), 1, 1, '') as InvNumList
    from vw_XC_DocInfo_1 V
    order by V.VendorID
    )
select VendorId,
    VendorName,
    DocIdsList,
    InvNumList
from CTE
group by VendorId,
    VendorName,
    DocIdsList,
    InvNumList

Answer 1

稍微跳出框框的东西怎么样？

SELECT VendorId, VendorName,
  CASE WHEN COUNT(DocumentId)>1 
       THEN CAST(MIN(DocumentId) AS VARCHAR(MAX)) + ',' +
            CAST(MAX(DocumentId) AS VARCHAR(MAX))
       ELSE CAST(MIN(DocumentId) AS VARCHAR(MAX)) 
  END AS DocIdList,
  SUM(Amount) Amount,
  CASE WHEN COUNT(InvoiceNumber)>1
       THEN MIN(InvoiceNumber) + ',' + MAX(InvoiceNumber)
       ELSE MIN(InvoiceNumber)
  END AS INumList
FROM 
  (SELECT *,(ROW_NUMBER() OVER (PARTITION BY VendorId 
                                ORDER BY VendorId) - 1) / 2 AS seq
      FROM vw_XC_DocInfo_1) AS result
GROUP BY VendorId, VendorName, seq

演示 here .