22,210
社区成员
发帖
与我相关
我的任务
分享
if exists(select name from sys.objects where name = 'test')
drop table Test
go
CREATE table test(
productid int , orderid nvarchar(40) , shipmentquantity int ,
valueid nvarchar(10) , valuestr nvarchar(10) , productname nvarchar(20)
)
insert into test
select '45', '201403306241956', 1,'72',N'M',N'产品1' union ALL
select '48','201402195799492', 2, '73',N'L',N'产品2' union all
select '48','201403306241956', 2 , '75',N'XXL',N'产品2' union ALL
select '48','201402195799492', 2, '92',N'灰色',N'产品2'union all
select '48','201403306241956', 2,'94',N'黑色',N'产品2' union all
select '45','201403306241956',1 ,'99',N'咖啡色',N'产品1'
----执行语句---------------------
select a.productid , sum(a.shipmentquantity) as 数量, a.productname ,
stuff(( SELECT
',' + b.valueid
FROM test AS b
WHERE b.productid = a.productid
AND a.orderid = b.orderid
FOR xml PATH ('')), 1,1,'') as 属性参数,
stuff(( SELECT
',' + b.valuestr
FROM test AS b
WHERE b.productid = a.productid
AND a.orderid = b.orderid
FOR xml PATH ('')), 1,1,'') as 属性参数名称
from test as a
group by productid ,productname,a.orderid
/*
productid 数量 productname 属性参数 属性参数名称
----------- ----------- -------------------- ----------- ------------------------------------
45 2 产品1 72,99 M,咖啡色
48 4 产品2 73,92 L,灰色
48 4 产品2 75,94 XXL,黑色
(3 行受影响)
*/