combining multiple rows into one row

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

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

And that's the results;

You can also try one of the XML methods e.g.

SELECT DISTINCT Issue, Customfield, StringValues
FROM Jira.customfieldvalue v1
CROSS APPLY ( SELECT StringValues + ',' 
              FROM jira.customfieldvalue v2
              WHERE v2.Customfield = v1.Customfield 
                  AND v2.Issue = v1.issue 
              ORDER BY ID 
                  FOR XML PATH('') )  D ( StringValues )
WHERE customfield = 12534 

AND ISSUE = 19602

source : http://stackoverflow.com/questions/8005846/sql-server-combining-multiple-rows-into-one-row