SQL group_concat function in SQL Server [duplicate]

Solution 1:

1. FOR XML PATH trick and article
2. CLR User defined aggregate
3. for sql server prior version 2005 - temporary tables

An example of #1

DECLARE @t TABLE (EmpId INT, EmpName VARCHAR(100))
INSERT @t VALUES
(1, 'Mary'),(1, 'John'),(1, 'Sam'),(2, 'Alaina'),(2, 'Edward')
SELECT distinct
    EmpId,
    (
        SELECT EmpName+','
        FROM @t t2
        WHERE t2.EmpId = t1.EmpId
        FOR XML PATH('')
    ) Concatenated
FROM @t t1

How to strip the final comma - is on your own

A CLR aggregate c# code for #2

using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
using System.IO;

namespace DatabaseAssembly
{
    [Serializable]
    [SqlUserDefinedAggregate(Format.UserDefined,
        IsInvariantToNulls = true,
        IsInvariantToDuplicates = true,
        IsInvariantToOrder = true,
        MaxByteSize = -1)]
    public struct StringJoin : IBinarySerialize
    {
        private Dictionary<string, string> AggregationList
        {
            get
            {
                if (_list == null)
                    _list = new Dictionary<string, string>();
                return _list;
            }
        }
        private Dictionary<string, string> _list;

        public void Init()
        {

        }

        public void Accumulate(SqlString Value)
        {
            if (!Value.IsNull)
                AggregationList[Value.Value.ToLowerInvariant()] = Value.Value;

        }

        public void Merge(StringJoin Group)
        {
            foreach (var key in Group.AggregationList.Keys)
                AggregationList[key] = Group.AggregationList[key];
        }

        public SqlChars Terminate()
        {
            var sb = new StringBuilder();
            foreach (var value in AggregationList.Values)
                sb.Append(value);
            return new SqlChars(sb.ToString());
        }

        #region IBinarySerialize Members

        public void Read(System.IO.BinaryReader r)
        {

            try
            {
                while (true)
                    AggregationList[r.ReadString()] = r.ReadString();
            }
            catch (EndOfStreamException)
            {

            }
        }

        public void Write(System.IO.BinaryWriter w)
        {
            foreach (var key in AggregationList.Keys)
            {
                w.Write(key);
                w.Write(AggregationList[key]);
            }
        }

        #endregion
    }
}

Solution 2:

The chosen answer from @OlegDok's may return the correct result. But the performance can be terrible. This test scenario will illustrate it.

Creation of a temp table:

CREATE table #temp (EmpId INT, EmpName VARCHAR(100))
;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f)
INSERT #temp
SELECT EmpId, EmpName FROM (values(1, 'Mary'),(1, 'John'),(1, 'Sam')) x(EmpId, EmpName)
CROSS APPLY 
(SELECT top 2000 N FROM tally) y
UNION ALL
SELECT EmpId, EmpName FROM (values(2, 'Alaina'),(2, 'Edward')) x(EmpId, EmpName)
CROSS APPLY
(SELECT top 2000 N FROM tally) y

This is only 10.000 rows. But lots of identical EmpId.

This query in Oleg's answer took 64 seconds on my database.

SELECT distinct
    EmpId,
    (
        SELECT EmpName+','
        FROM #temp t2
        WHERE t2.EmpId = t1.EmpId
        FOR XML PATH('')
    ) Concatenated
FROM #temp t1

Distinct is not the correct way of cleaning up rows in this situation. To avoid this cartesian join, reduce the initial number of IDs before joining like this.

This is the correct way of handling this:

;WITH CTE as
(
  SELECT distinct EmpId
  FROM #temp
)
SELECT 
    EmpId,
    STUFF((
        SELECT ','+EmpName
        FROM #temp t2
        WHERE t2.EmpId = t1.EmpId
        FOR XML PATH('')
    ), 1,1,'') Concatenated
FROM CTE t1

This takes less than 1 second