关于SQL的行列转换并合并到一个字段内

[weixin_42066028]

各位大神：
请教一个问题，SQL如何将多行数据把其中一个列数据合并一行中多列合并到一列中。

01 王明 铅
01 王明 粉尘、噪声

合并后效果：
01 王明 铅+粉尘、噪声

请大神们，用动态SQL如何实现合并?

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] nvarchar(22),[name] nvarchar(22),[values] nvarchar(25))
Insert #T
select N'01',N'王明',N'铅' union all
select N'01',N'王明',N'粉尘、噪声'
Go
--测试数据结束
SELECT  [id] ,  [name],
        STUFF(( SELECT  '、' + #T.[values]  
                FROM    #T  
                WHERE   [id] = a.[id]  
              FOR  
                XML PATH('')  
              ), 1, 1, '') AS value  
FROM    #T a  
GROUP BY a.[id]  ,  [name]

[RINK_1]

with cte_1
as
(select *,ROW_NUMBER() over (order by (select 1)) as rn_1 from #T),

cte_2
as
(select A.*,SUBSTRING(item,number,CHARINDEX('、',item+'、',number)-number) as single_itme,number
from cte_1 A
join master.dbo.spt_values B ON CHARINDEX('、','、'+item,number)=number
where type='p'),

cte_3
as
(select *,ROW_NUMBER() over (partition by rn_1 order by number) as rn_2 from cte_2),

cte_4
as
(select id,name,item_combine
from
(select *,stuff((select distinct '+'+single_itme from cte_3 where rn_2=A.rn_2 and id=A.id for XML path('')),1,1,'') as item_combine 
from cte_3 A) as A
group by id,name,item_combine)

select id,name,
stuff((select '、'+item_combine from cte_4 where id=A.id and name=A.name for xml path ('')),1,1,'') as item_combine_final 
from cte_4 as A
group by id,name