SQL group_concat function in SQL Server [duplicate]

Question

If there is a table called employee

EmpID           EmpName ----------      ------------- 1               Mary 1               John 1               Sam 2               Alaina 2               Edward 

Result I need in this format:

EmpID           EmpName ----------      ------------- 1               Mary, John, Sam 2               Alaina, Edward 

Q: this record is in same Employee table. I have almost no experience using UDFs, stored procedures, I need to be done this thing through query.Is this possible without using UDFs, SP's.

Answer 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     } }