测试数据
DECLARE @Table1 TABLE(ID INT, Value INT)
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400)
查询
SELECT ID
,STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
FROM @Table1
WHERE ID = t.ID
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @Table1 t
GROUP BY ID
结果集
╔════╦═════════════════════╗
║ ID ║ List_Output ║
╠════╬═════════════════════╣
║ 1 ║ 100, 200, 300, 400 ║
╚════╩═════════════════════╝
SQL Server 2017及更高版本
如果您正在使用SQL Server 2017或更高版本,可以使用内置的SQL Server函数STRING_AGG创建逗号分隔的列表:
DECLARE @Table1 TABLE(ID INT, Value INT);
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400);
SELECT ID , STRING_AGG([Value], ', ') AS List_Output
FROM @Table1
GROUP BY ID;
结果集
╔════╦═════════════════════╗
║ ID ║ List_Output ║
╠════╬═════════════════════╣
║ 1 ║ 100, 200, 300, 400 ║
╚════╩═════════════════════╝
DECLARE @big_string varchar(max) = ''; SELECT @big_string += x.s + ',' FROM (VALUES ('string1'), ('string2'), ('string3')) AS x(s);
。现在展示结果:SELECT @big_string;
。就是这么简单。 - JohnyL