How to use GROUP BY to concatenate strings in SQL Server?

No CURSOR, WHILE loop, or User-Defined Function needed.

Just need to be creative with FOR XML and PATH.

[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

If it is SQL Server 2017 or SQL Server Vnext, SQL Azure you can use string_agg as below:

select id, string_agg(concat(name, ':', [value]), ', ')
from #YourTable 
group by id

using XML path will not perfectly concatenate as you might expect... it will replace "&" with "&" and will also mess with <" and "> ...maybe a few other things, not sure...but you can try this

I came across a workaround for this... you need to replace:

FOR XML PATH('')
)

with:

FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(MAX)')

...or NVARCHAR(MAX) if thats what youre using.

why the hell doesn't SQL have a concatenate aggregate function? this is a PITA.

I ran into a couple of problems when I tried converting Kevin Fairchild's suggestion to work with strings containing spaces and special XML characters (&, <, >) which were encoded.

The final version of my code (which doesn't answer the original question but may be useful to someone) looks like this:

CREATE TABLE #YourTable ([ID] INT, [Name] VARCHAR(MAX), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'Oranges & Lemons',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'1 < 2',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT  [ID],
  STUFF((
    SELECT ', ' + CAST([Name] AS VARCHAR(MAX))
    FROM #YourTable WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE 
     /* Use .value to uncomment XML entities e.g. &gt; &lt; etc*/
    ).value('.','VARCHAR(MAX)') 
  ,1,2,'') as NameValues
FROM    #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

Rather than using a space as a delimiter and replacing all the spaces with commas, it just pre-pends a comma and space to each value then uses STUFF to remove the first two characters.

The XML encoding is taken care of automatically by using the TYPE directive.

Another option using Sql Server 2005 and above

---- test data
declare @t table (OUTPUTID int, SCHME varchar(10), DESCR varchar(10))
insert @t select 1125439       ,'CKT','Approved'
insert @t select 1125439       ,'RENO','Approved'
insert @t select 1134691       ,'CKT','Approved'
insert @t select 1134691       ,'RENO','Approved'
insert @t select 1134691       ,'pn','Approved'

---- actual query
;with cte(outputid,combined,rn)
as
(
  select outputid, SCHME + ' ('+DESCR+')', rn=ROW_NUMBER() over (PARTITION by outputid order by schme, descr)
  from @t
)
,cte2(outputid,finalstatus,rn)
as
(
select OUTPUTID, convert(varchar(max),combined), 1 from cte where rn=1
union all
select cte2.outputid, convert(varchar(max),cte2.finalstatus+', '+cte.combined), cte2.rn+1
from cte2
inner join cte on cte.OUTPUTID = cte2.outputid and cte.rn=cte2.rn+1
)
select outputid, MAX(finalstatus) from cte2 group by outputid

Install the SQLCLR Aggregates from http://groupconcat.codeplex.com

Then you can write code like this to get the result you asked for:

CREATE TABLE foo
(
 id INT,
 name CHAR(1),
 Value CHAR(1)
);

INSERT  INTO dbo.foo
    (id, name, Value)
VALUES  (1, 'A', '4'),
        (1, 'B', '8'),
        (2, 'C', '9');

SELECT  id,
    dbo.GROUP_CONCAT(name + ':' + Value) AS [Column]
FROM    dbo.foo
GROUP BY id;

Eight years later... Microsoft SQL Server vNext Database Engine has finally enhanced Transact-SQL to directly support grouped string concatenation. The Community Technical Preview version 1.0 added the STRING_AGG function and CTP 1.1 added the WITHIN GROUP clause for the STRING_AGG function.

Reference: https://msdn.microsoft.com/en-us/library/mt775028.aspx

SQL Server 2005 and later allow you to create your own custom aggregate functions, including for things like concatenation- see the sample at the bottom of the linked article.

An example would be

In Oracle you can use LISTAGG aggregate function.

Original records

name   type
------------
name1  type1
name2  type2
name2  type3

Sql

SELECT name, LISTAGG(type, '; ') WITHIN GROUP(ORDER BY name)
FROM table
GROUP BY name

Result in

name   type
------------
name1  type1
name2  type2; type3

This is just an addition to Kevin Fairchild's post (very clever by the way). I would have added it as a comment, but I don't have enough points yet :)

I was using this idea for a view I was working on, however the items I was concatinating contained spaces. So I modified the code slightly to not use spaces as delimiters.

Again thanks for the cool workaround Kevin!

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], 
       REPLACE(REPLACE(REPLACE(
                          (SELECT [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) as A 
                           FROM   #YourTable 
                           WHERE  ( ID = Results.ID ) 
                           FOR XML PATH (''))
                        , '</A><A>', ', ')
                ,'<A>','')
        ,'</A>','') AS NameValues 
FROM   #YourTable Results 
GROUP  BY ID 

DROP TABLE #YourTable 

This kind of question is asked here very often, and the solution is going to depend a lot on the underlying requirements:

https://stackoverflow.com/search?q=sql+pivot

and

https://stackoverflow.com/search?q=sql+concatenate

Typically, there is no SQL-only way to do this without either dynamic sql, a user-defined function, or a cursor.

Just to add to what Cade said, this is usually a front-end display thing and should therefore be handled there. I know that sometimes it's easier to write something 100% in SQL for things like file export or other "SQL only" solutions, but most of the times this concatenation should be handled in your display layer.

Don't need a cursor... a while loop is sufficient.

------------------------------
-- Setup
------------------------------

DECLARE @Source TABLE
(
  id int,
  Name varchar(30),
  Value int
)

DECLARE @Target TABLE
(
  id int,
  Result varchar(max) 
)


INSERT INTO @Source(id, Name, Value) SELECT 1, 'A', 4
INSERT INTO @Source(id, Name, Value) SELECT 1, 'B', 8
INSERT INTO @Source(id, Name, Value) SELECT 2, 'C', 9


------------------------------
-- Technique
------------------------------

INSERT INTO @Target (id)
SELECT id
FROM @Source
GROUP BY id

DECLARE @id int, @Result varchar(max)
SET @id = (SELECT MIN(id) FROM @Target)

WHILE @id is not null
BEGIN
  SET @Result = null

  SELECT @Result =
    CASE
      WHEN @Result is null
      THEN ''
      ELSE @Result + ', '
    END + s.Name + ':' + convert(varchar(30),s.Value)
  FROM @Source s
  WHERE id = @id

  UPDATE @Target
  SET Result = @Result
  WHERE id = @id

  SET @id = (SELECT MIN(id) FROM @Target WHERE @id < id)
END

SELECT *
FROM @Target

Let's get very simple:

SELECT stuff(
    (
    select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb 
    FOR XML PATH('')
    )
, 1, 2, '')

Replace this line:

select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb

With your query.

You can improve performance significant the following way if group by contains mostly one item:

SELECT 
  [ID],

CASE WHEN MAX( [Name]) = MIN( [Name]) THEN 
MAX( [Name]) NameValues
ELSE

  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

END

FROM #YourTable Results
GROUP BY ID

didn't see any cross apply answers, also no need for xml extraction. Here is a slightly different version of what Kevin Fairchild wrote. It's faster and easier to use in more complex queries:

   select T.ID
,MAX(X.cl) NameValues
 from #YourTable T
 CROSS APPLY 
 (select STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
    FROM #YourTable 
    WHERE (ID = T.ID) 
    FOR XML PATH(''))
  ,1,2,'')  [cl]) X
  GROUP BY T.ID

Using the Stuff and for xml path operator to concatenate rows to string :Group By two columns -->

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 (1,'B',5)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

-- retrieve each unique id and name columns and concatonate the values into one column
SELECT 
  [ID], 
  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) -- CONCATONATES EACH APPLICATION : VALUE SET      
    FROM #YourTable 
    WHERE (ID = Results.ID and Name = results.[name] ) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID


SELECT 
  [ID],[Name] , --these are acting as the group by clause
  STUFF((
    SELECT ', '+  CAST([Value] AS VARCHAR(MAX)) -- CONCATONATES THE VALUES FOR EACH ID NAME COMBINATION 
    FROM #YourTable 
    WHERE (ID = Results.ID and Name = results.[name] ) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS  NameValues
FROM #YourTable Results
GROUP BY ID, name

DROP TABLE #YourTable

Using Replace Function and FOR JSON PATH

SELECT T3.DEPT, REPLACE(REPLACE(T3.ENAME,'{"ENAME":"',''),'"}','') AS ENAME_LIST
FROM (
 SELECT DEPT, (SELECT ENAME AS [ENAME]
        FROM EMPLOYEE T2
        WHERE T2.DEPT=T1.DEPT
        FOR JSON PATH,WITHOUT_ARRAY_WRAPPER) ENAME
    FROM EMPLOYEE T1
    GROUP BY DEPT) T3

For sample data and more ways click here

If you have clr enabled you could use the Group_Concat library from GitHub

Another example without the garbage: ",TYPE).value('(./text())[1]','VARCHAR(MAX)')"

WITH t AS (
    SELECT 1 n, 1 g, 1 v
    UNION ALL 
    SELECT 2 n, 1 g, 2 v
    UNION ALL 
    SELECT 3 n, 2 g, 3 v
)
SELECT g
        , STUFF (
                (
                    SELECT ', ' + CAST(v AS VARCHAR(MAX))
                    FROM t sub_t
                    WHERE sub_t.g = main_t.g
                    FOR XML PATH('')
                )
                , 1, 2, ''
        ) cg
FROM t main_t
GROUP BY g

Input-output is

*************************   ->  *********************
*   n   *   g   *   v   *       *   g   *   cg      *
*   -   *   -   *   -   *       *   -   *   -       *
*   1   *   1   *   1   *       *   1   *   1, 2    *
*   2   *   1   *   2   *       *   2   *   3       *
*   3   *   2   *   3   *       *********************
*************************   

I used this approach which may be easier to grasp. Get a root element, then concat to choices any item with the same ID but not the 'official' name

  Declare @IdxList as Table(id int, choices varchar(max),AisName varchar(255))
  Insert into @IdxLIst(id,choices,AisName)
  Select IdxId,''''+Max(Title)+'''',Max(Title) From [dbo].[dta_Alias] 
 where IdxId is not null group by IdxId
  Update @IdxLIst
    set choices=choices +','''+Title+''''
    From @IdxLIst JOIN [dta_Alias] ON id=IdxId And Title <> AisName
    where IdxId is not null
    Select * from @IdxList where choices like '%,%'

For all my healthcare folks out there:

 
SELECT
s.NOTE_ID
,STUFF ((
        SELECT
           [note_text] + ' ' 
        FROM
            HNO_NOTE_TEXT s1
        WHERE
            (s1.NOTE_ID = s.NOTE_ID)
        ORDER BY [line] ASC
         FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
         ,
        1,
        2,
        '') AS NOTE_TEXT_CONCATINATED
FROM
    HNO_NOTE_TEXT s
    GROUP BY NOTE_ID