Pivot (dynamically)!

(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;

Screen Shot 2017-05-18 at 11.57.32 AM

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;

Screen Shot 2017-05-18 at 11.56.44 AM

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;

Screen Shot 2017-05-18 at 11.58.40 AM

 

 

 

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s