How to use GROUP BY to concatenate strings in SQL Server?

kingschan

于 2015-06-11 10:34:26 发布

阅读量281

点赞数

分类专栏： mssql 文章标签： sqlserver group_contract

本文链接：https://blog.youkuaiyun.com/kingschan/article/details/84723875

版权

mssql 专栏收录该内容

26 篇文章

订阅专栏


CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
  [ID],
  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable