Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Script to create dynamic PIVOT queries in SQL Server

MSSQLTips author Aaron Bertrand By:   |   Read Comments (21)   |   Related Tips: More > T-SQL
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


Last Update: 10/16/2012


About the author
MSSQLTips author Aaron Bertrand
Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for over a decade, first earning the Microsoft MVP award in 1997.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, July 10, 2014 - 7:46:49 PM - maideen Read The Tip

hi Mr.aaron

I have tried your sample in my environment. Mine is different from what you poste. 

Thanks I got idea from your code and tried in mine one. working fine. but in the sigle value

I could not do range like year 2010 to 2013.

Pls advice me.

Thanking you in advance 

Below is my sp

 

ALTER PROCEDURE [dbo].[Z_usp_Circ_Details_Year] 

@vYEAR varchar(100)

AS

BEGIN

SET NOCOUNT ON

Declare @sSQL varchar(1000)

Set @sSQL = 'SELECT * FROM 

(SELECT  MEMBER,[LANGUAGE],[TYPE],[PLATFORM],copies,CATEGORY,MAINAREA,MonthName,YearNo

 FROM Z_tbl_Circulation_Details) TableYearNo

pivot (Sum(copies) for YearNo IN (['+@vYEAR+'])) pivotTable'

exec(@sSQL)


Tuesday, January 28, 2014 - 7:00:32 AM - hardik rawal Read The Tip

 Its such a good script and usefull to me.

 

Thanks & Regards,
Hardik Rawal



Wednesday, October 09, 2013 - 10:06:09 AM - vikram Read The Tip

select tel.link from(Select tel.link from
trn_ewf_Links tel
join trn_customer_form_Header tcfh
on  
tcfh.form_id= tel.form_id)As s
PIVOT
(
FOR [tel.link] in (URL1,URL2,URL3,URL4,URL5)
)As pivot

I am trying to make the column Link resultshorizontal and join it to another table.Can some one please tellme the error here?I am new to SQL.So may be a simple mistake but not sure.


Wednesday, July 17, 2013 - 2:53:36 AM - OMAN Read The Tip

 

 can any one explaine me this parts of the codes?

SELECT @columns += N', p.' + QUOTENAME(Name)
&&

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

 


 


Wednesday, May 22, 2013 - 2:47:01 AM - Munir Read The Tip

Thanks Aaron Bertrand

You are right. It was compatibility issue.


Munir


Tuesday, May 21, 2013 - 7:50:29 AM - Aaron Bertrand Read The Tip

Munir, it sounds like your database is set to 80 (2000) compatibility. Some elements of newer T-SQL syntax are only available in newer compatibility levels, so you should consider changing that (look up ALTER DATABASE) if you're not keeping the old compat for a specific reason.


Tuesday, May 21, 2013 - 4:40:28 AM - munir Read The Tip

 

Its looks a wounder full artical, but i am having some erro details given below, Please help me out this

 

my procedure is as:

 

SELECT

 

p.[Aaj News], p.[ARY Nnews], p.[BBC News], p.[Dunya News], p.[Express News], p.[Geo News], p.[Samma News]

 

FROM

 

(

SELECT p.ChannelName, o.CurPositionFROM dbo.tblchannel AS pINNERJOIN dbo.tblChannelPosition AS oON p.Id = o.ChannelId

)

 

AS j

 

PIVOT

 

(

avg(CurPosition)FOR ChannelName IN([Aaj News],[ARY Nnews],[BBC News],[Dunya News],[Express News],[Geo News],[Samma News])

)

 

AS p;

 

I am having given below error : please help

 

 

Msg 325, Level 15, State 1, Line 12

 

Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

 


Thursday, March 14, 2013 - 3:00:53 PM - Erin Read The Tip

Correction:  for your example, I could just use the product table in the select to create the @column variable instead of joining it with the orderdetails table.  For my solution, I didn't have a table that already contained a distinct list of my pivot table headers.


Thursday, March 14, 2013 - 2:47:07 PM - Erin Read The Tip

Figured it out.  To put the column names in alpha order, I had to add a step.  I selected the distinct product names in alpha order into another table then created the @column variable form that table instead of the products table.


Thursday, March 14, 2013 - 2:28:36 PM - Erin Read The Tip

How would you force the product columns to be in alpha order after the pivot:   i.e.  Bar, Blat, Foo Kin.   Right now, they come out as:  Foo, Bar, Kin, Blat


Thursday, February 07, 2013 - 11:59:02 AM - Chris F Read The Tip

Thanks, this was extremely helpful and exactly what I needed.


Friday, November 09, 2012 - 12:43:52 PM - Rafael Trotta Read The Tip

Aaron, I understood. That's true. You're right! 
Thanks for your explanation! And sorry my disattention.


Friday, November 09, 2012 - 10:20:20 AM - Aaron Bertrand Read The Tip

Rafael, if I had chosen nvarchar(4000) I would agree with you. Nvarchar(max), however, can store 2 GB worth of data (about 1 billion characters). If your products table is that large you probably shouldn't be pivoting like this.


Friday, November 09, 2012 - 9:51:18 AM - Rafael Trotta Read The Tip

This example is a great solution to the proposed problem, however i identified some possible limitations. I believe that your solution could not works if you have a lot of items on products table, because your script depends on the amount of characters contained in the product name column. Remember that the maximum size of NVARCHAR is 8,000 characters and your dynamic SQL concatenates command and products name before execute variable @SQL. So, if you have forty products, each one with 255 characters, you will have 10,200 characters (255 x 40) that probably cause an error in your script because it exceeds the maximum size of NVARCHAR and truncates the command.


Friday, October 19, 2012 - 9:18:51 AM - Aaron Bertrand Read The Tip

Adrian yes, in most cases I would suggest using stored procedures even outside of this scenario.


Thursday, October 18, 2012 - 11:13:42 PM - Adrian Read The Tip

This is a great example. How would you use it though in SSRS report? There, we are not allowed to use variables and therefore we cannot user the above query. Maybe a stored procedure?

 


Wednesday, October 17, 2012 - 10:55:25 AM - Ryan Read The Tip

Thanks, gentlemen!  That was the correction I needed to make.

Great article, Aaron.


Tuesday, October 16, 2012 - 9:13:11 PM - YoungHa Read The Tip

IF you chane like this :

SET @sql = 'SELECT OrderID,' +@columns+ @CR+
'FROM
(
  SELECT o.OrderID, 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 ('  + REPLACE(@columns, 'p.[', '[')  + ')
) AS p
order by OrderID ;' ;

you can get the pivot table ordered by OrderID.


Tuesday, October 16, 2012 - 11:21:04 AM - Aaron Bertrand Read The Tip

Sorry about that. I tried to edit the code to be < 2008 compatible. On 2005 Greg is right, you'll need SELECT @columns = @columns + so that every column is added, not just the first.


Tuesday, October 16, 2012 - 11:12:01 AM - Greg Robidoux Read The Tip

Ryan - what version of SQL Server are you usings?  The += is the same as doing SELECT @columns = @columns + ....

Try this line:

SELECT @columns = @columns + N', p.' + QUOTENAME(Name)


Tuesday, October 16, 2012 - 10:24:49 AM - Ryan Read The Tip

I had to change the line:

SELECT @columns = N', p.' + QUOTENAME(Name)

to get rid of the '+' but after that, the dynamic SQL only produces the pivot for [blat]:

 SELECT 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 ([blat])
) AS p;

Am I doing something wrong?




 
Sponsor Information