Sql server2008将同类型或同名的数量汇总并拼接字符串

Sql server2008将同类型或同名的数量汇总并拼接字符串

select NAME,Model,Unit,Price,Amount,StorageID from Inventory

这里要求将同Name的合并成一条，数量Amount相加，然后对应的StorageID对应的Amount拼接成一个新的字段，如1:131;2:10

Amount相加容易，SUM函数即可
SELECT NAME,Model,Unit,Price,SUM(Amount)as ‘总数’ FROM Inventory GROUP BY NAME ,Type,Model,Unit,Price

需要用到：STUFF函数，CAST函数

select NAME,Model,c.Unit,Price,SUM(Amount) AS ‘总数’,
stuff((select ‘;’+CAST(StorageId as varchar)+’:’+CAST(Amount as varchar) from Inventory
where c.NAME=NAME
for xml path(’’)),1,1,’’) as ‘具体’
from Inventory c
group by c.NAME,c.Type,c.Model,c.Unit,c.Price

CAST函数避免拼接时两个字段数据类型不同而导致报错