在SQL Server中是否有可能将多个结果合并为单个字符串?
我有以下表:
NameID Name
------ -----
1 Alvin
2 Alan
3 Ben
4 Tom
我该如何选择表格并返回以下所示的选择结果:
Group
-----
Alvin, Alan, Ben and Tom
在SQL Server中是否有可能将多个结果合并为单个字符串?
我有以下表:
NameID Name
------ -----
1 Alvin
2 Alan
3 Ben
4 Tom
Group
-----
Alvin, Alan, Ben and Tom
STRING_AGG
函数来简化这个过程:SELECT STRING_AGG(Name,', ') FROM Table
Alvin, Alan, Ben, Tom
If it is absolutely necessary to inject the and in the end, then we could use a replace last instance of routine:
NOTE: this level of formatting is much better managed in the rendering logic, not the SQL data logic.
SELECT STUFF(names,LastIndex,2,' and ') FROM ( SELECT STRING_AGG(Name,', ') names FROM Table ) as raw CROSS APPLY (SELECT LEN(names)-CHARINDEX(' ,', REVERSE(names)) as LastIndex) as Helper
Returns:
Alvin, Alan, Ben and Tom
FOR XML PATH
的方法。SELECT Stuff((SELECT ', ' + NAME
FROM Yourtable
FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
value
方法帮助我们在结果中避免对 xml
保留字符 进行编码。
试试这个:
DECLARE @a TABLE
(Col1 int,
Col2 varchar(5)
);
--
DECLARE @b VARCHAR(500) = '';
--
INSERT INTO @a (Col1, Col2)
VALUES (1,'Alvin'),
(2,'Alan'),
(3,'Ben'),
(4,'Tom');
--
SELECT @b = COALESCE(@b,'') + Col2 + ', '
FROM @a;
--
SELECT STUFF(STUFF(@b, LEN(@b), 1, ''), LEN(@b) + 1 - CHARINDEX(',', REVERSE(STUFF(@b, LEN(@b), 1, ''))), 1, ' and')