(Do you have the scene from Friends where Ross tries to move the couch stuck in your head?  Because I do.)

Pivot is a handy tool in SQL Server for turning rows into columns, just like you would in Excel.  The downside is that it doesn’t accept in variables directly, but this can be managed using Dynamic SQL.

The old way.  (Note – using the AdventureWorksLT2012 schema)

Step 1: Find my pivot category list

SELECT DISTINCT ISNULL(color, 'Uncolored') FROM SalesLT.Product;

Step 2: Paste in my list to my Pivot Statement

SELECT * FROM 
(SELECT P.productid, PC.Name, ISNULL(P.color, 'Uncolored') as Color
FROM SalesLT.ProductCategory as PC
JOIN SalesLT.Product as P
ON PC.ProductCategoryID = p.ProductCategoryID
) as PPC
PIVOT(COUNT(ProductID) FOR Color IN([Red],[Blue],[Black],[Silver],[Grey],[Multi],[Uncolored])) as colors
ORDER BY Name;

While this works, if I have any new colors they are not automatically added to my list.  Using sp_executesql, I can pass in a group string to create my updated color list:

DECLARE @groupcolors nvarchar(4000)
DECLARE @Colors AS TABLE (Color nvarchar(15));

INSERT INTO @Colors
SELECT DISTINCT ISNULL(color, 'Uncolored') FROM SalesLT.Product;
SELECT @groupcolors = COALESCE(@groupcolors+',[' ,'[') + CONVERT(nvarchar(15), Color) + ']' from @Colors;
--SELECT @groupcolors;

DECLARE @SQLString AS NVARCHAR(4000)
SET @SQLString = N'
SELECT * FROM 
(SELECT P.productid, PC.Name, ISNULL(P.color, ''Uncolored'') as Color
FROM SalesLT.ProductCategory as PC
JOIN SalesLT.Product as P
ON PC.ProductCategoryID = p.ProductCategoryID
) as PPC
PIVOT(COUNT(ProductID) FOR Color IN (' + @groupcolors + ')) as Color
ORDER BY Name;'
EXEC sp_executesql 
 @statement = @SQLString
 , @params = N'@groupcolors NVARCHAR(4000)'
 , @groupcolors = @groupcolors;