asp classic - SQL group_concat function in SQL Server -

this question has answer here:

• simulating group_concat mysql function in microsoft sql server 2005? 9 answers

if there table called employee

empid           empname ----------      ------------- 1               mary 1               john 1               sam 2               alaina 2               edward 

result need in format:

empid           empname ----------      ------------- 1               mary, john, sam 2               alaina, edward 

q: record in same employee table. have no experience using udfs, stored procedures, need done thing through query.is possible without using udfs, sp's.

1. for xml path trick , 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+','         @t t2         t2.empid = t1.empid         xml path('')     ) concatenated @t t1 

how strip final comma - on own

a clr aggregate c# code #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         {                         {                 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     } }