22,210
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:@表A
declare @表A table([ID] int,[主机] varchar(4),[资产名称] varchar(10),[金额] numeric(6,2),[备注] varchar(8),[编号] varchar(3),[数量] int,[日期] datetime)
insert @表A
select 1,'主机','会议桌',1000.56,'会议用','QC1',4,'1990-1-1' union all
select 1,null,'屏风',2000,'屏风配件','QC2',2,'1992-1-1' union all
select 2,'主机','E卓',2000.33,'111','QC3',5,'1990-1-1' union all
select 2,null,'桌板',500,'222','QC4',3,'1982-1-1' union all
select 2,null,'桌脚',500,'333','QC5',10,'2000-1-1' union all
select 2,null,'电话',500,'444','QC6',1,'2010-1-1' union all
select 3,'主机','员工办公桌',5000.22,'aa','QC7',8,'1988-1-1' union all
select 3,null,'活动椅',2000.11,'bb','QC8',1,'2001-1-1' union all
select 3,null,'键盘架',1000.55,'cc','QC9',1,'2005-1-1'
select
ID ,[主机]=(SELECT COUNT([主机]) FROM @表A WHERE id=a.id),
[资产名称]=(SELECT TOP 1 [资产名称] FROM @表A WHERE id=a.id AND [主机]='主机'),
[金额]=SUM([金额]),
[备注]=stuff((select ','+[备注] from @表A t where a.id=t.id for xml path('')), 1, 1, ''),
[编号]=stuff((select ','+[编号] from @表A t where a.id=t.id for xml path('')), 1, 1, ''),
[数量]=(SELECT TOP 1 [数量] FROM @表A WHERE id=a.id AND [主机]='主机'),
[日期]=(SELECT TOP 1 [日期] FROM @表A WHERE id=a.id AND [主机]='主机')
from @表A a group by id