源表格：
------------------------
部位 物料 用量
A XXX 100
B XXX 200
C XXX 300
------------------------
如何用SQL合计转为以下：
------------------------
部位 物料 用量
A+B+C XXX 600
------------------------
请高手帮忙，谢谢!
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-27 16:29:34
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[HUANG]
if object_id('[HUANG]') is not null drop table [HUANG]
go
create table [HUANG]([部位] nvarchar(2),[物料] nvarchar(6),[用量] int)
insert [HUANG]
select 'A','XXX',100 union all
select 'B','XXX',200 union all
select 'C','XXX',300
--------------生成数据--------------------------
select stuff((select '+'+[部位] from [HUANG] b
where b.[物料]=a.[物料]
for xml path('')),1,1,'') '部位',a.[物料],SUM(a.[用量])[用量]
from [HUANG] a
group by a.[物料]
----------------结果----------------------------
/*
部位 物料 用量
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ -----------
A+B+C XXX 600
*/
------解决方案--------------------
--sql2005
select 物料,
(select 部位+'+' from tb b where a.物料 = b.物料 for xml path('')) as 部位,
sum(用量) 用量