3

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jin Yong
  • 42,698
  • 72
  • 141
  • 187
  • 2
    Possible duplicate of [Convert Database Rows into Columns](https://stackoverflow.com/questions/1946234/convert-database-rows-into-columns) – RoMEoMusTDiE Jan 17 '18 at 02:47
  • telling us your rdbms would be helpful. in oracle it is listagg in sql server you can do it with xml path, in mysql it is group_concat – Bryan Dellinger Jan 17 '18 at 02:49
  • Always helpful to add a tag for the particular verrsion of SQL Server you are using. T-SQL is evolving with each version, and it's possible that certain versions have different solutions to your problem. – TT. Jan 17 '18 at 03:34

4 Answers4

2

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 Helper

Returns:

Alvin, Alan, Ben and Tom
Chris Schaller
  • 13,704
  • 3
  • 43
  • 81
1

Here 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

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
1

Try this:

  SELECT
  SUBSTRING((SELECT
    ',' + CAST(Name AS varchar)
  FROM table_name
  FOR xml PATH (''))
  , 2, 10000) AS Group1

enter image description here

Laxmi
  • 3,830
  • 26
  • 30
  • `Name` will be truncated to 30 characters due to missing length `cast`. Also what if the name list is greater than 10000 characters – Pரதீப் Jan 17 '18 at 04:29
0

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

Richard S
  • 21
  • 1