Script to create dynamic PIVOT queries in SQL Server

By:   |   Updated: 2012-10-16   |   Comments (37)   |   Related: 1 | 2 | 3 | More > T-SQL


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.


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

USE tempdb;
CREATE TABLE dbo.Products
  /* other columns */
INSERT dbo.Products VALUES
(1, N'foo'),
(2, N'bar'),
(3, N'kin');
CREATE TABLE dbo.OrderDetails
  OrderID INT,
    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]
  SELECT p.Name, o.Quantity
   FROM dbo.Products AS p
   INNER JOIN dbo.OrderDetails AS o
   ON p.ProductID = o.ProductID
) AS j
  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]
  SELECT p.Name, o.Quantity
   FROM dbo.Products AS p
   INNER JOIN dbo.OrderDetails AS o
   ON p.ProductID = o.ProductID
) AS j
  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:

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, '') + '
  SELECT p.Name, o.Quantity
   FROM dbo.Products AS p
   INNER JOIN dbo.OrderDetails AS o
   ON p.ProductID = o.ProductID
) AS j
  SUM(Quantity) FOR Name IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;


SELECT p.[foo], p.[bar], p.[kin], p.[blat]
  SELECT p.Name, o.Quantity
   FROM dbo.Products AS p
   INNER JOIN dbo.OrderDetails AS o
   ON p.ProductID = o.ProductID
) AS j
  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 Updated: 2012-10-16

get scripts

next tip button

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog,, and also blogs at

View all my tips

Comments For This Article

Tuesday, October 27, 2020 - 11:14:33 AM - jahyree Back To Top (86702)
trivial? you just blew my mind buddy.

Friday, October 02, 2020 - 5:58:52 PM - Mildred Back To Top (86589)
I need to ask someone about a script that I am working on and I cannot get it to work. It is a script that has one table that have a single row for an ID and two tables with multiple rows. I can get ne table to act correctly, but the second multiple rows table I cannot get it to sum a column if meets specific criteria. Who can I get to help me?

Wednesday, December 04, 2019 - 2:40:58 PM - David Gillett Back To Top (83288)


Great sp!  Thanks.  I had some trouble understanding what the input parameters meant.  I changed them to make them in-your-face-obvious.  This might help make the proc more approachable:

CREATE PROCEDURE dbo.usp_Dyna_Pivot (

   @Columns NVARCHAR (100),

   @Table NVARCHAR (100),

   @Function NVARCHAR (3),

   @Values NVARCHAR (100),

   @Rows NVARCHAR (100)


Thanks for all your inspiration!

Wednesday, August 21, 2019 - 8:30:43 PM - deadlift_pro Back To Top (82118)

Thanks Aaron. Exactly what I was looking for.

Tuesday, December 05, 2017 - 9:51:48 AM - Jerry Back To Top (73645)

 Nice!  Worked like a charm.  Greatly appreciated.


Tuesday, September 27, 2016 - 11:23:27 AM - Claudio Kerber Back To Top (43432)


Thank you Aaron, you did not only helped me with dynamic PIVOT but also teached me on how to build a query and then running it.

I'm generating a report with totals and the amount of months varies from processed file to processed file. The result of applying this post was beautiful.



Tuesday, August 09, 2016 - 11:04:32 AM - Timothy Wright Back To Top (43087)

 I don't know if this article is still being monitored because it  was started years ago, but since it can't hurt to ask, I will.  

I am trying to do a dynamic pivot but the variables I need to create have to be calculated values of the contents in the dataset.  For example, suppose that in your example dataset, you also had a column called WEIGHT which is 100 for case1, 200 for case2, and 300 for case3.

Instead of Foo Bar Kin having the values of 2, 2, 1, respectively, I need Foo to have the value of 200, BAR to have the value of 400 and KIN is 300. Even better would be a way to keep Foo as 2, Bar as 2, Kin as 1, and add Weighted_Foo as 200, Weighted_Bar as 200 and Weighted_Kin as 300.  

Can anyone help with this?




Thursday, March 24, 2016 - 4:35:27 PM - Craig Back To Top (41052)

Thank you for posting this...  this is great! 


Sunday, January 17, 2016 - 3:11:02 PM - Aaron Bertrand Back To Top (40433)

@Noone I'm note sure you can't simply send this code as a batch from VB.Net?

Also the += syntax was introduced in SQL Server 2008 (there were some comments below about this earlier). If you have compilation errors due to that, I suspect you are actually using 2005.

Saturday, January 16, 2016 - 12:55:13 PM - Noone Ofimport Back To Top (40431)

Can this be run in VB.Net?

I can't create a stored procedure in the SQL database (I can, but I don't have access to do so).  I know this is an older post, but I can't find anything on doing this anywhere.

As far as the code itself, this is a great example, I was able to get it to work two ways... as noted above for SS 2012 (as the "+=" was not availabe before 2012) and also in SS 2008.

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

2008 - SELLECT @columns = COALESCE(@columns +', ', '') +QUOTENAME(Name)

Also, I had some additional complexity in what I needed and added several WHERE conditions in both INNER JOIN SELECT statements with variables passed in - worked great.

If I could just get this code to run from VB.Net I could die a happy man!

(that was an extreme overexageration made reasonable in my mind by the deep level of desperation I'm currenlty experiencing. In reality, dying a happy man will take slightly more than solving this problem)

Tuesday, October 20, 2015 - 10:45:11 AM - Tech Bangalore Back To Top (38942)

I have slightly complex situation.  Trying to use UNPIVOT with 'WITH'.  The Assignment table has 4 quarters.  But I want one quarter at a time so I can UNION the query for other quarters.  The reason I want UNION is, I need to be able to display some calculated values against each quarter, and label the column as Q1_Targer, Q2_Target etc.  There could be a better way, but I'm trying to resolve the first step.  The problem is, I'm unable to specify just one quarter in the WITH column list, since I want only one quarter in UNPIVOT.  If I mention just Q1 in the column list or just Q, get this message, "'Cte' has fewer columns than were specified in the column list."  To get rid of this, I need to mentione all quarters in SP in UNPIVOT.

PS: I have a large query with JOINs from different table, below the WITH clause.  

        WITH Cte( Region, EN, Q1,Q2,Q3,Q4, SP, RN)  AS



FROM [dbo].[Assignment_2015] a




) upt


Monday, September 14, 2015 - 2:44:25 AM - SWATI AGRAWAL Back To Top (38669)

I have done this to 2008 sql server .

Now I want to do same as attendance of worker with seems like attendance date on (foo),(bin) and so on & then we show the status of attendance like P or A as depend on status 


I have a table with 4 field (ID),(WorkerID),(AttendanceDate),(AttendanceStatus) & Have Another Table With Fields like (WorkerId),(WorkerName)



So I want to Show that


WorkerName   09-13-2015   09-14-2015   09-15-2015,....

Swati Agrawal      A                   P             P

 Agrawal             A                   P             P

Ag Agrawal          P                   A             P

How could i done this

Saturday, September 12, 2015 - 10:03:40 AM - Aaron Bertrand Back To Top (38661)

SWATI, sorry, not sure. Check to make sure you are using a version of SQL Server that supports PIVOT (2005+) and that the database where you're running the script isn't in 80 compatibility mode.

Saturday, September 12, 2015 - 6:33:33 AM - SWATI AGRAWAL Back To Top (38660)

Thursday, December 04, 2014 - 8:07:58 AM - Aaron Bertrand Back To Top (35502)

@Brian, a view cannot contain dynamic SQL or declared variables. A table-valued function (which can often act like a parameterized view) can have variables but it can't have dynamic SQL. Why exactly does this need to be a view?

Wednesday, December 03, 2014 - 4:44:40 PM - Brian Back To Top (35492)

I know this article is old but I was wonderting how to create a view with this capability. I know I cannot create a view with declared variables so I need to find a way to create a dynamic pivot querie without them. Any ideas?

Thursday, July 10, 2014 - 7:46:49 PM - maideen Back To Top (32642)

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)




Declare @sSQL varchar(1000)



 FROM Z_tbl_Circulation_Details) TableYearNo

pivot (Sum(copies) for YearNo IN (['[email protected]+'])) pivotTable'


Tuesday, January 28, 2014 - 7:00:32 AM - hardik rawal Back To Top (29251)

 Its such a good script and usefull to me.


Thanks & Regards,
Hardik Rawal

Wednesday, October 09, 2013 - 10:06:09 AM - vikram Back To Top (27090)

select from(Select from
trn_ewf_Links tel
join trn_customer_form_Header tcfh
tcfh.form_id= tel.form_id)As s
FOR [] 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 Back To Top (25871)


 can any one explaine me this parts of the codes?

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


@sql =N'SELECT '+STUFF(@columns, 1, 2,'')+




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 Back To Top (24072)

Thanks Aaron Bertrand

You are right. It was compatibility issue.


Tuesday, May 21, 2013 - 7:50:29 AM - Aaron Bertrand Back To Top (24066)

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 Back To Top (24056)


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


my procedure is as:




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





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



AS j





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 Back To Top (22803)

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 Back To Top (22802)

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 Back To Top (22800)

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 Back To Top (21987)

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

Friday, November 09, 2012 - 12:43:52 PM - Rafael Trotta Back To Top (20275)

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 Back To Top (20273)

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 Back To Top (20272)

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 Back To Top (19995)

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 Back To Top (19990)

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 Back To Top (19961)

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

Great article, Aaron.

Tuesday, October 16, 2012 - 9:13:11 PM - YoungHa Back To Top (19946)

IF you chane like this :

SET @sql = 'SELECT OrderID,' [email protected]+ @CR+
  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
  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 Back To Top (19944)

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 Back To Top (19943)

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 Back To Top (19941)

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]
  SELECT p.Name, o.Quantity
   FROM dbo.Products AS p
   INNER JOIN dbo.OrderDetails AS o
   ON p.ProductID = o.ProductID
) AS j
  SUM(Quantity) FOR Name IN ([blat])
) AS p;

Am I doing something wrong?


Recommended Reading

Cross tab queries with SQL Server 2000

Crosstab queries using PIVOT in SQL Server

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

get free sql tips
agree to terms