如何使用 GROUP BY 在 SQL Server 中连接字符串? [英] How to use GROUP BY to concatenate strings in SQL Server?

问题描述

我如何获得:

id       Name       Value
1          A          4
1          B          8
2          C          9

到

id          Column
1          A:4, B:8
2          C:9

推荐答案

不需要 CURSOR、WHILE 循环或用户定义函数.

只需要对 FOR XML 和 PATH 发挥创意即可.

Just need to be creative with FOR XML and PATH.

[注意:此解决方案仅适用于 SQL 2005 及更高版本.原始问题未指定使用的版本.]

[Note: This solution only works on SQL 2005 and later. Original question didn't specify the version in use.]

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

这篇关于如何使用 GROUP BY 在 SQL Server 中连接字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！