Microsoft® SQL Server® 2012 Bible

398

Part IV: Programming with T-SQL

This type of data retrieval is quite common. Often a vertical list of values is better reported

as a single comma-delimited horizontal list than as a subreport or another subheading level

several inches long. A short horizontal list is more readable and saves space.

The following example builds a list of departments in the AdventureWorks2012 sample

database from the HumanResources.Department table:

USE AdventureWorks2012;

DECLARE @MAV varchar(max)

SELECT @MAV = Coalesce(@MAV + ', '+ d.Name, d.Name)

FROM (

SELECT Name

FROM HumanResources.Department

)

ORDER BY d.Name;

SELECT @MAV;

Result (line breaks added for readability):

---------------------------------------------------------------------

Document Control, Engineering, Executive, Facilities and Maintenance,

Finance, Human Resources, Information Services, Marketing, Production,

Production Control, Purchasing, Quality Assurance, Research and

Development, Sales, Shipping and Receiving, Tool Design

Using COALESCE

If you’re not familiar with COALESCE, it’s a worthy part of T-SQL to learn. It evaluates each comma-

separated value immediately following the keyword, using the fi rst one it encounters that doesn’t

have a NULL value. It is quite similar to ISNULL, but is able to evaluate more than two items. In the

above case, it is checking the value of @MAV + ', '+d.Name, and if it is NULL, will use the next value,

d.Name. This means that in this particular case, the string that is built will start out with a depart-

ment name rather than a leading comma (because @MAV will start out as NULL), and all subsequent

assignments of this variable will have the concatenated comma, thus allowing COALESCE to use the

fi rst item in the list.

The problem with multiple assignment variables is that Microsoft is vague about their

behavior. The order of the denormalized data isn’t guaranteed, but queries do seem to

respond to the ORDER BY clause. It’s not documented in Books Online, but it has been

documented in Microsoft Knowledge Base article 287515. It performs well, but be cautious

about using it when the result is order-dependent.

c16.indd 398c16.indd 398 7/30/2012 5:38:07 PM7/30/2012 5:38:07 PM

http://www.it-ebooks.info