Is it possible to combine multiple results into a single string in SQL Server?
I have the following table:
NameID Name
------ -----
1 Alvin
2 Alan
3 Ben
4 Tom
How can I select the table and return the select result as below:
Group
-----
Alvin, Alan, Ben and Tom
SQL Server 2017 introduced the STRING_AGG to simplify this process:
SELECT STRING_AGG(Name,', ') FROM Table
This will return:
Alvin, Alan, Ben, Tom
There is a good summary on these concepts here as well: SQL Server: Concatenate Multiple Rows Into Single String
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 HelperReturns:
Alvin, Alan, Ben and Tom
Try this:
SELECT
SUBSTRING((SELECT
',' + CAST(Name AS varchar)
FROM table_name
FOR xml PATH (''))
, 2, 10000) AS Group1
Name will be truncated to 30 characters due to missing length cast. Also what if the name list is greater than 10000 charactersHere is one way using FOR XML PATH
SELECT Stuff((SELECT ', ' + NAME
FROM Yourtable
FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
value method helps us to avoid encoding of xml reserved characters in the result
Try this:
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')
Output:
Alvin, Alan, Ben and Tom
