27,579
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([付款单号] nvarchar(27),[金额] int,[票据号] nvarchar(24),[票据金额] int)
Insert #T
select N'PA00016',1000,N'T001',200 union all
select N'PA00016',1000,N'T002',700 union all
select N'PA00016',1000,N'T003',500 union all
select N'PA00016',1000,N'T004',800 union all
select N'PA00017',1200,N'T001',200 union all
select N'PA00017',1200,N'T002',700 union all
select N'PA00017',1200,N'T003',500 union all
select N'PA00017',1200,N'T004',800
Go
--测试数据结束
;WITH cte AS (
SELECT
付款单号,
金额,
STUFF(
(
SELECT
',' + [票据号]
FROM
#T
WHERE
付款单号 = a.付款单号
FOR XML PATH('')
), 1, 1, ''
) AS [票据号],
SUM(票据金额) 票据金额
FROM
#T a
GROUP BY
付款单号,
金额)
SELECT
STUFF(
(
SELECT
',' + 付款单号
FROM
cte
WHERE
票据号 = a.票据号
FOR XML PATH('')
), 1, 1, ''
) 付款单号,
SUM(a.金额) 金额,
票据号,
a.票据金额
FROM
cte a
GROUP BY
票据号,
a.票据金额;