SQL Server Query for displaying more than one record in a cell

Question

I have 2 tables:

Employee-

EmployeeID      Title       EmployeeFirstName       EmployeeLastName
1001            Mr          Peter                   Parker  
1002            Ms          Nancy                   Hall

HoursWorked-

EmployeeID      HoursWorked
1001            15
1001            30
1001            45
1002            15
1002            30
1002            40

I have written Following query:

Select Distinct 
E.EmployeeID EmployeeID,
E.Title Title,
E.FirstName EmployeeFirstName,
E.LastName EmployeeLastName,
HW.HoursWorked HoursWorked,
From Employee E
Inner Join HoursWorked HW ON E.EmployeeId = HW.EmployeeId

which gives me following output:

EmployeeID      Title       EmployeeFirstName       EmployeeLastName        HoursWorked
1001            Mr          Peter                   Parker                  15
1001            Mr          Peter                   Parker                  30
1001            Mr          Peter                   Parker                  45
1002            Ms          Nancy                   Hall                    15
1002            Ms          Nancy                   Hall                    30
1002            Ms          Nancy                   Hall                    40

I want to display the records in following format:

EmployeeID      Title       EmployeeFirstName       EmployeeLastName        HoursWorked
1001            Mr          Peter                   Parker                  15,30,45
1002            Ms          Nancy                   Hall                    15,30,40

Please let me know if how can I do this?

Take a look at [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string). — 3N1GM4, Dec 19 '16 at 10:26

3N1GM4 · Answer 1 · 2016-12-19T11:14:50.680

Use FOR XML to combine the values:

SELECT
    E.EmployeeID,
    E.Title,
    E.FirstName,
    E.LastName,
    STUFF(
            (
                SELECT ',' + CONVERT(VARCHAR,HW.HoursWorked)
                FROM HoursWorked HW
                WHERE E.EmployeeId = HW.EmployeeId
                GROUP BY HW.HoursWorked
                ORDER BY HW.HoursWorked
                FOR XML PATH('')
            ), 1, 1, ''
         ) AS HoursWorked
FROM Employee E
GROUP BY E.EmployeeID,
         E.Title,
         E.FirstName,
         E.LastName

SQL Server Query for displaying more than one record in a cell

1 Answers1