需求:如下图:
上图中的数据合并要达到下图的效果:即数字项和字符串项都相加,但是字符串重复的需要去重。
select * from ( select orderno, STUFF((select ','+valuecurrency from #table where a.orderno=Orderno group by valuecurrency for xml path('')),1,1,'') as valuecurrencyz,
sum(DeclarationValue)DeclarationValuez,SUM(quantity)quantity,
STUFF((select ','+nameen from #table where a.orderno=Orderno group by nameen for xml path('')),1,1,'') as nameenz
from #table a group by a.orderno) b
核心点:STUFF((select ','+valuecurrency from #table where a.orderno=Orderno group by valuecurrency for xml path('')),1,1,'') as valuecurrencyz, 其中group by 用于合并去重。