MSSQL字段按照符号拼接显示

按照指定符号拼接显示

先看效果：

--法一:
DECLARE @symbolStr nvarchar(1)
set @symbolStr=',';
with Temp as
(
select '123' name1,'1das23' name2 union
select '123' name1,'1fw23' name2 union
select '1232eqw13' name1,'12few3' name2 union
select '12dsad3' name1,'1fw23' name2 union
select '12dsa3' name1,'12few3' name2 union
select '12das3' name1,'1few23' name2 

)
--根据相同的name1的，将name2逗号拼接显示
SELECT  B.name1 ,
        LEFT(StuList, LEN(StuList) - 1) AS showContent
FROM    ( SELECT    name1 ,
                    ( SELECT    showContent + @symbolStr
                      FROM      ( SELECT    name1 ,
                                            name2 showContent
                                  FROM      Temp
                                ) t
                      WHERE     t.name1 = A.name1
                    FOR
                      XML PATH('')
                    ) AS StuList
          FROM  Temp A
          GROUP BY  name1
        ) B
ORDER BY B.name1;
--法二：
--将name1、name2 连接后逗号拼接显示
DECLARE @showContent NVARCHAR(MAX),@connectorStr nvarchar(100)
SET @showContent = '';
set @symbolStr=',';
set @connectorStr='-';
with Temp as
(
select '123' name1,'1das23' name2 union
select '123' name1,'1fw23' name2 union
select '1232eqw13' name1,'12few3' name2 union
select '12dsad3' name1,'1fw23' name2 union
select '12dsa3' name1,'12few3' name2 union
select '12das3' name1,'1few23' name2 

)
SELECT @showContent = @showContent + @symbolStr +CONVERT(NVARCHAR(50),name1)+@connectorStr +name2 FROM Temp 
SELECT SUBSTRING(@showContent,2,LEN(@showContent)) AS Gnames