Coalesce in SQL Server

The Many Uses of Coalesce in SQL Server.


Using Coalesce to Pivot
If you run the following statement against the AdventureWorks database SELECT Name
FROM HumanResources.Department
WHERE (GroupName 'Executive General and Administration')

DECLARE @DepartmentName VARCHAR(1000)

SELECT @DepartmentName COALESCE(@DepartmentName,'') + Name ';'
FROM HumanResources.Department
WHERE (GroupName 'Executive General and Administration')

SELECT @DepartmentName AS DepartmentNames

and get the following result set.

kill all the transactions in a database using three lines of code. If you have ever tried to restore a database and could not obtain exclusive access, you know how useful this can be.


SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '
FROM sys.sysprocesses
WHERE DBID=DB_ID('AdventureWorks')

PRINT @SQL --EXEC(@SQL) Replace the print statement with exec to execute

will give you a result set such as the following.

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s