34,590
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([编号] int,[顺序号] int,[代码] int,[部门] int,[借金额] int,[贷金额] int,[摘要] nvarchar(200))
Insert #T
select 101,1,100301,4,500,0,N'张三' union all
select 101,2,200001,0,500,null,null union all
select 102,1,100301,4,300,0,N'王五' union all
select 102,2,100301,5,400,0,N'陈六' union all
select 102,3,200001,0,700,null,null union all
select 103,1,100401,2,600,0,N'周七' union all
select 103,2,200801,0,600,null,null
Go
Select [编号]=MAX([编号]),[顺序号]=MAX([顺序号]),[代码],[部门],SUM([借金额]) AS [借金额],ISNULL(RTRIM(SUM([贷金额])),'') AS [贷金额],
ISNULL(STUFF((SELECT ISNULL('、'+[摘要],'') FROM #T WHERE [代码]=a.[代码] AND [部门]=a.[部门] FOR XML PATH('')),1,1,''),'') AS [摘要]
from #T AS a
GROUP BY [代码],[部门]
ORDER BY 1,2
/*
编号 顺序号 代码 部门 借金额 贷金额 摘要
102 1 100301 4 800 0 张三、王五
102 2 100301 5 400 0 陈六
102 3 200001 0 1200
103 1 100401 2 600 0 周七
103 2 200801 0 600
*/
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([编号] int,[顺序号] int,[代码] int,[部门] int,[借金额] int,[贷金额] int)
Insert #T
select 101,1,100301,4,500,0 union all
select 101,2,200001,0,500,null union all
select 102,1,100301,4,300,0 union all
select 102,2,100301,5,400,0 union all
select 102,3,200001,0,700,null
Go
Select [编号]=MAX([编号]),[顺序号]=MAX([顺序号]),[代码],[部门],SUM([借金额]) AS [借金额],SUM([贷金额]) AS [贷金额]
from #T
GROUP BY [代码],[部门]
ORDER BY 2
/*
编号 顺序号 代码 部门 借金额 贷金额
102 1 100301 4 800 0
102 2 100301 5 400 0
102 3 200001 0 1200 NULL
*/