Generate comma-separated value as a part of GROUP BY

This is not a new thing but it is a common requirement with many implementations, so making a post on it.

Assume that you need to get some values in the group as a comma-separated value instead of running them in an aggregate function. There are  many ways of handling this, some are complex and some are straight-forward. If it is not a complex GROUP BY and table is relatively small, following is the best I have used.

Let's say, we have an EmployeeTelephoneNumbers table like below;

EmployeeId	PhoneNumber
1	0777222333
2	0777343565
2	072345345
2	0112456456
3	071578976
3	01154672121

And need to produce a result like this;

EmployeeId	PhoneNumber
1	0777222333
2	0777343565, 072345345, 0112456456
3	071578976, 01154672121

As usual, we have to use GROUP BY and perform a different logic for forming the second column without using an aggregate function. For that, first we need to use is FOR XML clause, which allows us to convert the tabular resultset into an XML. It has different modes such as RAW, AUTO, EXPLICIT and PATH and PATH5 has to be used with this.

For more info on PATH: https://msdn.microsoft.com/en-us/library/ms189885.aspx

Next is STUFF function. This function inserts a string into another string deleting the given length.

For more info on STUFF function: https://msdn.microsoft.com/en-us/library/ms188043.aspx

What we have to do is, use FOR XML PATH for generating an XML that contains all values for grouping and using it as a string. Then STUFF will be used for removing the extra characters.

Here is a sample code.

USE tempdb;
GO

-- creating the table
CREATE TABLE dbo.EmployeeTelephoneNumbers
(
 EmployeeId int not null
 , PhoneNumber varchar(200) not null
);
GO

-- Inserting sample records
INSERT INTO dbo.EmployeeTelephoneNumbers
 VALUES (1, '0777222333'), (2, '0777343565'), (2, '072345345'), (2, '0112456456'), (3, '071578976'), (3, '01154672121');
GO

-- Generate the result
SELECT 
 EmployeeId
 , STUFF((SELECT ', ' + PhoneNumber
   FROM dbo.EmployeeTelephoneNumbers
   WHERE EmployeeId = t.EmployeeId
   FOR XML PATH('')), 1, 2, '') PhoneNumbers
 
FROM dbo.EmployeeTelephoneNumbers t
GROUP BY EmployeeId;