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;

When GROUP BY is not used, HAVING behaves like a WHERE clause

If you have gone through BOL for HAVING keyword, you have seen above sentence at the top of the page. What does it exactly mean? Similar question raised up during my class too; Seems like this sentence makes all of us confused :).

It says that HAVING behaves like WHERE when GROUP BY is not used. Then where we can use HAVING without using GROUP BY? You just cannot replace WHERE with HAVING. But for some extent, HAVING can be used without GROUP BY. Think a about a scenario where you need to see whether number of orders placed by customers reach to certain level, let’s say 100, if so, get the number of orders. If there is a requirement like that, you can write a T-SQL like below;

USE AdventureWorks2008

GO

SELECT COUNT(*)

FROM Sales.SalesOrderHeader

HAVING Count(*) > 100

Here we have used HAVING without GROUP BY. What has happened here is, the entire row-set is considered as a group, hence HAVING works fine. I did not come across any other places this can be used, please share with me if you have.