Monday, July 7, 2008

Using Coalesce to Pivot

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')

you will come up with a standard result

If you want to pivot the data you could run the following command.
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 result set with comma separated in a single column.

No comments: