How to concatenate multiple rows?

Question

I have the following query which returns the salary of all employees. This work perfectly but I need to collect extra data that I will aggregate into one cell (see Result Set 2).

How can I aggregate data into a comma separated list? A little bit like what Sum does, but I need a string in return.

SELECT Employee.Id, SUM(Pay) as Salary
FROM Employee
INNER JOIN PayCheck ON PayCheck.EmployeeId = Employee.Id
GROUP BY Employee.Id

Result Set 1

Employee.Id              Salary
-----------------------------------
          1                 150
          2                 250
          3                 350

I need:

Result Set 2

Employee.Id              Salary                 Data
----------------------------------------------------
          1                 150      One, Two, Three
          2                 250      Four, Five, Six
          3                 350      Seven

Answer 1

For SQL Server 2005+, use the STUFF function and FOR XML PATH:

WITH summary_cte AS (
   SELECT Employee.Id, SUM(Pay) as Salary
     FROM Employee
     JOIN PayCheck ON PayCheck.EmployeeId = Employee.Id
 GROUP BY Employee.Id)
SELECT sc.id, 
       sc.salary,
       STUFF((SELECT ','+ yt.data
                FROM your_table yt
               WHERE yt.id = sc.id
            GROUP BY yt.data
             FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
  FROM summary_cte sc

But you're missing details about where the data you want to turn into a comma delimited string is, and how it relates to an employee record...