source: http://www.MSSQLTips.com/tip.asp?id=2783 -- printed: 9/4/2015 11:42:28 PM

Script to create dynamic PIVOT queries in SQL Server

Written By: Aaron Bertrand -- 10/16/2012

Problem

Pivoting (or producing a "cross-tab") is a common reporting requirement - data is stored in columns and you need to present it in rows. This was a nice feature that was added to SQL Server, but you don't always know all of the values you need to pivot on.  In this tip we look at how you can dynamically create the pivot command to handle these unknown values.

Solution

In these cases it may make sense to construct a dynamic pivot. Let's assume the following simple schema:

USE tempdb;
GO
CREATE TABLE dbo.Products
(
  ProductID INT PRIMARY KEY,
  Name      NVARCHAR(255) NOT NULL UNIQUE
  /* other columns */
);
INSERT dbo.Products VALUES
(1, N'foo'),
(2, N'bar'),
(3, N'kin');
CREATE TABLE dbo.OrderDetails
(
  OrderID INT,
  ProductID INT NOT NULL
    FOREIGN KEY REFERENCES dbo.Products(ProductID),
  Quantity INT
  /* other columns */
);
INSERT dbo.OrderDetails VALUES
(1, 1, 1),
(1, 2, 2),
(2, 1, 1),
(3, 3, 1);

A query to obtain each product's total quantity ordered would look something like this (ignoring details about order date, etc.):

SELECT p.Name, Quantity = SUM(o.Quantity)
  FROM dbo.Products AS p
  INNER JOIN dbo.OrderDetails AS o
  ON p.ProductID = o.ProductID
  GROUP BY p.Name;

And in this case the results would look like this:

Actual results from grouped query

But what if the results needed to look like this?

Desired results for report

We could write a hard-coded PIVOT query, but only if we know all of the potential product names:

SELECT p.[foo], p.[bar], p.[kin]
FROM
(
  SELECT p.Name, o.Quantity
   FROM dbo.Products AS p
   INNER JOIN dbo.OrderDetails AS o
   ON p.ProductID = o.ProductID
) AS j
PIVOT
(
  SUM(Quantity) FOR Name IN ([foo],[bar],[kin])
) AS p;

If we add a new product, this PIVOT query no longer gives us the complete story. And we certainly don't want to have to go back and edit these queries every time we add a product (never mind when a product's name is changed). So consider some new data:

INSERT dbo.Products SELECT 4, N'blat';
INSERT dbo.OrderDetails SELECT 4,4,5;

Obviously the hard-coded PIVOT query is not going to pick up this new product:

SELECT p.[foo], p.[bar], p.[kin]
FROM
(
  SELECT p.Name, o.Quantity
   FROM dbo.Products AS p
   INNER JOIN dbo.OrderDetails AS o
   ON p.ProductID = o.ProductID
) AS j
PIVOT
(
  SUM(Quantity) FOR Name IN ([foo],[bar],[kin])
) AS p;

Results are the same as before:

Results from hard-coded PIVOT

Enter dynamic SQL. Typically frowned upon, this is one of the scenarios where you are likely to make a strong case for its use. We can simply build the output / pivot column list at runtime, and append it to the rest of the query:

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(Name)
  FROM (SELECT p.Name FROM dbo.Products AS p
  INNER JOIN dbo.OrderDetails AS o
  ON p.ProductID = o.ProductID
  GROUP BY p.Name) AS x;
SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT p.Name, o.Quantity
   FROM dbo.Products AS p
   INNER JOIN dbo.OrderDetails AS o
   ON p.ProductID = o.ProductID
) AS j
PIVOT
(
  SUM(Quantity) FOR Name IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

Results:

SELECT p.[foo], p.[bar], p.[kin], p.[blat]
FROM
(
  SELECT p.Name, o.Quantity
   FROM dbo.Products AS p
   INNER JOIN dbo.OrderDetails AS o
   ON p.ProductID = o.ProductID
) AS j
PIVOT
(
  SUM(Quantity) FOR Name IN ([foo],[bar],[kin],[blat])
) AS p;

Results for dynamic PIVOT

This is a fairly trivial example, but I hope it demonstrates one approach you could use to achieve dynamic PIVOT.

Next Steps
 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.