22,209
社区成员
发帖
与我相关
我的任务
分享
create table test(部位 nvarchar(10),物料 nvarchar(10),用量 int)
insert test
select 'A','XXX',100 union all
select 'B','XXX',200 union all
select 'C','XXX',300
SELECT data=STUFF((SELECT ','+部位 FROM test t
WHERE 物料=t1.物料 FOR XML PATH('')), 1, 1, ''),
物料,
用量=SUM(用量)
FROM test t1
GROUP BY 物料
/*
A,B,C XXX 600
*/
WITH a1 (zd1,zd2,zd3) AS
(
SELECT 'A','XXX',100 UNION ALL
SELECT 'B','XXX',200 UNION ALL
SELECT 'C','XXX',300
)
SELECT STUFF((SELECT '+'+zd1 FROM a1 WHERE zd2=a.zd2 FOR XML PATH('')),1,1,'') zd1,zd2,SUM(zd3) d3
FROM a1 a
GROUP BY zd2
--sql2005
select 物料,
(select 部位+'+' from tb b where a.物料 = b.物料 for xml path('')) as 部位,
sum(用量) 用量
from tb a
group by 物料
----------------------------------------------------------------
-- 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
*/