Crosstab queries using PIVOT in SQL Server
By: Greg Robidoux | Comments (68) | Related: 1 | 2 | More > Query Optimization
Problem
The need to create cross tab queries like you would do in Excel is something that could be handy with SQL Server data. In this article we look at using PIVOT to create cross tab results.
Solution
We will use the SQL Server PIVOT to create cross-tab results and show an example.
Let's say we have data in a table that looks like this:
SalesPerson | Product | SalesAmount |
---|---|---|
Bob | Pickles | $100.00 |
Sue | Oranges | $50.00 |
Bob | Pickles | $25.00 |
Bob | Oranges | $300.00 |
Sue | Oranges | $500.00 |
And what we want to do is to rearrange the output to look like below. First you have the data rows such as SalesPerson, and then columns for each Product with the summarized SalesAmount value for each cross section.
SalesPerson | Oranges | Pickles |
---|---|---|
Bob | $300.00 | $125.00 |
Sue | $550.00 |
Let's create a sample table and insert some data that we can use with a PIVOT query.
CREATE TABLE ProductSales ( SalesPerson varchar(20), Product varchar(20), SalesAmount money ) GO INSERT INTO ProductSales SELECT 'Bob','Pickles',100.00 UNION SELECT 'Sue','Oranges',50.00 UNION SELECT 'Bob','Pickles',25.00 UNION SELECT 'Bob','Oranges',300.00 UNION SELECT 'Sue','Oranges',500.00 GO
Here is a simple PIVOT query that allows us to pull the cross-tab results as shown above.
SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles FROM ( SELECT SalesPerson, Product, SalesAmount FROM ProductSales ) ps PIVOT ( SUM (SalesAmount) FOR Product IN ( [Oranges], [Pickles]) ) AS pvt
So how does this work?
There are three pieces that need to be understood in order to construct the query.
- (1) The SELECT statement
- SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
- This portion of the query selects the three columns for the final result set (SalesPerson, Oranges, Pickles)
- (2) The query that pulls the raw data to be prepared
- (SELECT SalesPerson, Product, SalesAmount FROM ProductSales) ps
- This query pulls all the rows of data that we need to create the cross-tab results. The (ps) after the query is creating a temporary table of the results that can then be used to satisfy the query for step 1.
- (3) The PIVOT expression
- PIVOT (SUM (SalesAmount) FOR Product IN ( [Oranges], [Pickles]) ) AS pvt
- This query does the actual summarization and puts the results into a temporary table called pvt
Another key thing to notice in here is the use of the square brackets [ ] around the column names in both the SELECT in part (1) and the IN in part (3). These are key, because the pivot operation is treating the values in these columns as column names and this is how the breaking and grouping is done to display the data.
Next Steps
- Take a look at the new PIVOT option that SQL Server offers to see how you can use this
- Try to write some more complex queries to take advantage of this new option.
- Also take a look at this tip that dynamically creates the PIVOT query instead of having to hard code the query.
About the author

View all my tips
Comments For This Article
Friday, May 10, 2019 - 1:46:59 PM - John DiMartino | Back To Top (80034) |
Thanks for the article. It was very informative and helpful. I look forward to future articles. |
Tuesday, March 26, 2019 - 8:51:02 AM - Greg Robidoux | Back To Top (79398) |
Hi Carolina, thanks for pointing out the issue. The link has been fixed in the article. -Greg |
Monday, March 25, 2019 - 7:19:53 PM - Carolina Ragazzon | Back To Top (79395) |
Error 404 for Link "https://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/" Unable to find the dynamic query cos pivoting several columns, especially when content is unknown, is not usefull. |
Friday, March 22, 2019 - 4:52:43 PM - Greg Robidoux | Back To Top (79377) |
Thanks Rick. I just made a few updates so hopefully it makes more sense to someone new to this. -Greg |
Friday, March 22, 2019 - 4:05:15 PM - Rick | Back To Top (79376) |
I have agonized in the past every time I wanted to create a pivot table. This is by far, the most understandable explanation I've ever seen and helped me tremendously. I'm no longer afraid to tackle these tasks because I understand step by step what happens during a pivot. Thank you! Rick |
Friday, February 9, 2018 - 9:58:06 AM - Greg Robidoux | Back To Top (75153) |
Hi Chip, take a look at this tip: https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/
|
Friday, February 9, 2018 - 9:56:11 AM - Chip | Back To Top (75152) |
This example only works if the products are static, e.g. [Oranges] AS Oranges, [Pickles] AS Pickles so unfortuantely not that useful.
|
Friday, February 10, 2017 - 7:00:31 AM - Greg Robidoux | Back To Top (46158) |
Gopal, this is for SQL Server not sure if this syntax works for MySQL. -Greg |
Friday, February 10, 2017 - 4:48:07 AM - Gopi Krishna | Back To Top (46154) |
select * from(select Store,Week,xCount from yt)src pivot(sum(xCount) for Week in ([1], [2], [3]))piv;
why isn't working is there any software problem it's showing -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql version' at line 1 |
Monday, April 11, 2016 - 6:05:03 AM - Miley Devlin | Back To Top (41183) |
Hey Greg, great example. Helped me sort out a Cross Tab Qry for multiple currencies.
Kind Regards Miley |
Tuesday, January 19, 2016 - 10:04:25 AM - Durugesh | Back To Top (40443) |
i executed below query and output as per below
70
i want each row results total marks as given above |
Wednesday, July 22, 2015 - 4:35:38 AM - HvH | Back To Top (38272) |
hi Robidoux, thanks for excellent guidance. i have a script is there any way count sum for rows and columns.Thanks appreciate it
SELECT * FROM (select SMS_CHANNELS.SMS_CHANNEL_DESC [CHANNEL DESC], LEFT(DATENAME(MONTH,SMS_CUSTOMER_REGISTER_DATE),3) [MONTH], COUNT(1) [TOTAL] FROM SMS_CUSTOMER with (nolock),SMS_CHANNELS with(nolock) where SMS_CUSTOMER.SMS_CHANNEL=SMS_CHANNELS.SMS_CHANNEL_ID and year(SMS_CUSTOMER_REGISTER_DATE)=year(getdate()) and month(SMS_CUSTOMER_REGISTER_DATE) group by Left(datename(MONTH, SMS_CUSTOMER_REGISTER_DATE),3),SMS_CHANNELS.SMS_CHANNEL_DESC) AS CUST PIVOT ( SUM([TOTAL]) FOR MONTH IN ([JAN],[FEB],[MAR],[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC]))AS PVT1 |
Thursday, April 9, 2015 - 1:52:16 AM - Danny Solorzano | Back To Top (36870) |
Mr Robidoux, Thanks for your response. I tried the code you wrote and have only one thing to say to.... WOW!!! That worked liked magic. I'm going to play with this to see if I can get more info onto this query. I may reach out to you again in the future, if that's ok with you, of course. Much Appreciated! Danny |
Tuesday, April 7, 2015 - 6:06:54 PM - Greg Robidoux | Back To Top (36851) |
Hi Danny, try this to see if this works for you. I just got rid of the single quotes around the values. SELECT |
Tuesday, April 7, 2015 - 4:24:51 PM - Danny Solorzano | Back To Top (36847) |
Hi, This was a great help! However, Like the previous poster, Im getting only NULL values in my data. Can you please see if anything jumps out at you?
SELECT val_dt, Proj_plan_cd, Policy_id, ['PPSSET'] AS 'stat_res', ['TXSSET'] AS 'tax_res'
FROM (SELECT val_dt, Proj_plan_cd, Policy_id, data_src, sum(con_res+non_con_res) as 'Total_Rsv' FROM gpval_bnft_piece_str_reserves where val_dt = '01-01-2015' and data_src in ('PPSSET', 'TXSSET') and scenario_cd in ('01','02') and substring(proj_plan_cd,1,1) <> 'J' group by val_dt, Proj_plan_cd, Policy_id, data_src) ps
PIVOT( sum(Total_Rsv) FOR data_src IN ( ['PPSSET'], ['TXSSET'])) AS pvt
Thanks, Danny |
Tuesday, February 24, 2015 - 8:25:21 AM - Brooks | Back To Top (36331) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
This is an excellent article but I can't get it to work when the data is text. The output is showing as NULL but there is data present. Can you offer advice? Thank you! SELECT [DESTFLD] ,[AFS] as AFS,[CLCS] as CLCS,[CPI] as CPI,[ForeignOfficeDeposits] as ForeignOfficeDeposits,[HEQ] as HEQ ,[ILN] as ILN,[INVSEC] as InvSec,[Mansec_Swaps] as Mansec_Swaps,[Manual] as Manualx,[Manual_SBO] as Manual_SBO ,[QRMLiab] as QRMLiab,[TDA] as TDA FROM (select [DESTFLD],[SDM_System],[SDM_Field] from [Reporting].[dbo].[vw_QRM_Transformations_Staging]) ps
pivot (max( [SDM_field]) for [SDM_System] in ([AFS],[CLCS],[CPI],[ForeignOfficeDeposits],[HEQ] ,[ILN],[INVSEC] ,[Mansec_Swaps] ,[Manual],[Manual_SBO] ,[QRMLiab],[TDA]) ) as pvt
|
Thursday, February 12, 2015 - 9:15:06 AM - Greg Robidoux | Back To Top (36213) |
Hi Menaka, I am not really sure what you are trying to do from your example. Not sure how the result set is created based on the records you inserted. -Greg |
Wednesday, February 11, 2015 - 4:46:06 PM - Menaka | Back To Top (36209) |
Hi Mr. Robidoux I need to group the 2 column values and give them a group id. ex: create table test (pubno int, memberno int, grpno int) insert into test (pubno, memberno) values (1,5);
GrpID member Many thanks |
Thursday, October 9, 2014 - 5:10:50 AM - joshi987 | Back To Top (34900) |
hi Mr. Robidoux i need to create rows by using calculations of using existing rows 1. x (logic is sum revenue ( month) acno>10 and and acno<90 and acno=95 2.y (logic is 14% revenue (month) 3. z (x-y)
My data is in following formate ac no jan feb march april may..... 1 23 34 46 34 54 2 89 76 92 84 13 3 4 5 6 7 ................................ my out put will be like jan feb march april may............................ x y z plz help me regarding this issue |
Wednesday, September 17, 2014 - 8:12:43 PM - Vincent | Back To Top (34571) |
Hello Sir this is my table. course - date - trainer - day tamil - 1/2/14 - a - 1 tamil - 2/2/14 - a+b - 2 english - 1/2/14 - c - 1 english - 2/2/14 - d+e - 2 tamil - 2/2/14 - f - 1 tamil - 3/2/14 - g+h -2
i want to show as follows please help us.
course - 1/2/14 - 2/2/14 - 3/2/14 tamil - a - a+b f g+h english c d+e
|
Monday, September 1, 2014 - 8:33:08 AM - Greg Robidoux | Back To Top (34343) |
Hi Venkat, take a look at this tip: http://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/ this should do what you are looking for. -Greg |
Monday, September 1, 2014 - 4:12:51 AM - venkat | Back To Top (34340) |
I have data in a table which shows the follwing information Code Day JL 1 JL 5 JL6 JL9 How can i show this like this Code Day1 Day2Day3day4 JL 1 5 6 9 Please help on this , I need Sqlserver Code |
Friday, July 11, 2014 - 6:55:36 AM - JS | Back To Top (32654) |
Hi,
I have data in a table which shows the follwing information Code Day JL 1 JL 5
How can i show this like this JL 1 5
Thanks |
Tuesday, July 8, 2014 - 9:50:46 AM - Greg Robidoux | Back To Top (32590) |
Hi Kristina, you should be able to just do this: SELECT col1, col2, col3, etc. INTO newTableName FROM xxxxx You can look at this tip as well for creating a dynamic pivot: http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/, but it looks like you may have figured that out already. |
Monday, July 7, 2014 - 11:56:50 PM - Kristina | Back To Top (32585) |
How do I create a table with the results of a pivot query? I have transformed the data in my table with a dynamic pivot query but now I need to create a new table with the results. I feel this should be straightforward but somehow I am not managing it. Thanks. |
Saturday, June 21, 2014 - 7:15:49 AM - gaurav | Back To Top (32336) |
excelent queries.
|
Thursday, April 17, 2014 - 1:53:19 AM - Jayprakash | Back To Top (30088) |
Very good explanation. Thanks for the article.
|
Friday, April 11, 2014 - 4:49:49 PM - Jason | Back To Top (30055) |
Update: Greg, your answer was correct. My SSRS project was pointing to a test server that hadn't been updated with the form in production, and all is well. Thanks you so much for your help! Jason |
Thursday, April 10, 2014 - 4:57:11 PM - Jason | Back To Top (30040) |
Yes, that is exactly what I'm saying. If I look at the design view of my report on the data tab, I can SEE that the region has been added to the original set of data. When I switch to the preview tab, the 30 columns of data are missing and the added region data also does not display. For informational purposes, we're using: SQL Server 2005 SSRS (via Microsoft Visual Studio Express 2005) Thanks, Jason |
Thursday, April 10, 2014 - 4:41:44 PM - Greg Robidoux | Back To Top (30039) |
Hi Jason, so are you saying the SP returns the data correctly, but when you use the SP for the report the data is not shown correctly? |
Thursday, April 10, 2014 - 4:07:31 PM - Jason | Back To Top (30038) |
Thank you for your prompt response! I saw at the end of the SP that indeed it pulled to the #tmp table already, so I added the column and I saw the region data appear. However, now the problem is that when I preview my report in SSRS, the table does not show the region data and it does not display roughly 30 columns of data that displayed before the altering of the SP. Any ideas why this would be happening? |
Wednesday, April 9, 2014 - 4:41:27 PM - Greg Robidoux | Back To Top (30026) |
Jason - you could write the results of the SP to a temporary table and then join the temporary table to the table that has the additional column. |
Wednesday, April 9, 2014 - 3:19:39 PM - Jason | Back To Top (30025) |
Hey there, I have a SP with a pivot that I need to add a column that contains data from a table outside of the SP. To say it a different way, I need to add a column that contains the regions matching the id of the resulting SP output. The code is long, and is already posted at Stack Overflow in the following link: http://stackoverflow.com/questions/22969169/adding-a-column-to-a-pivoting-stored-procedure-in-ssrs-sqlserver-2005 . Any ideas?
Thanks for your help. |
Wednesday, March 5, 2014 - 9:19:13 AM - tanehome | Back To Top (29647) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
hi all, please assist in the below i've the below table for the attendance log and I need to the data to be as the next table, how can I use crrostab for this?
I need the result to be
|
Tuesday, February 18, 2014 - 2:15:25 PM - borys tyukin | Back To Top (29493) |
Greg, you did much better job explaining this than Microsoft :) I keep forgetting pivot syntax and even though i used it before many times, microsoft help page for PIVOT is no use. you did much better job and used simple language - I like that! |
Saturday, December 21, 2013 - 4:45:42 AM - Max Harris | Back To Top (27865) |
Brilliant, brilliant brilliant! Every time I use sql pivot I have to relearn it....I'll bookmark this page and it'll make the 'revisit' so much quicker and easier. Simple, concise and easy to understand, just what's needed. Thanks so very much. BW (and happy Christmas!) Max |
Tuesday, December 17, 2013 - 6:16:46 AM - kushal pardeshi | Back To Top (27816) |
customer(cust_id,c_name,c_city,c_country) product(prod_id,p_name,p_rate) order(order_id,cust_id,prod_id,quantity,ammount) this are my tables and i want following output country1 country2 country3 total p1 quantity quantity quantity ---- p2 quantity quantity quantity ---- p3 quantity quantity quantity ---- Total --- --- ---- -----
'p1,p2,p3' means product_id i.e. p_id ---- means the total of verticle and horizontal records |
Saturday, October 12, 2013 - 7:05:45 AM - A | Back To Top (27130) |
WHAT ADD A COLUMN WITM FUNCTION OTHER? |
Thursday, May 23, 2013 - 6:58:07 AM - Prabakaran | Back To Top (25097) |
Hai Rani Please Check with this Query, SELECT SRLNUB,[TRF]AS TRF ,[EVT]AS EVT FROM (SELECT SRLNUB, REVCOD, amount FROM fmtextbl ) ps PIVOT ( SUM(amount) FOR REVCOD IN([TRF],[EVT]) )AS pvt
Thanks, Prabakaran |
Saturday, April 27, 2013 - 12:46:12 AM - Aditya Singh | Back To Top (23590) |
I am trying same code to implement in PHP but its giving some error.
Table is clientdetails with 3 attributes (clientname,productname,quantity).
<?php
Its showing error as "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pivot (Sum(quantity) for productname in ('Software' ,'Desktop'))' at line 5 ".
Can someone help me in this? Thanks, Aditya Singh
|
Monday, April 22, 2013 - 3:24:22 AM - vani | Back To Top (23465) |
|
Monday, April 22, 2013 - 3:18:35 AM - VANI | Back To Top (23464) |
I Have tried the same but not working pls go through my query and let me know what is wrong with my query
And the query is as follows
SELECT SRLNUB,'Trf'AS TRF ,'EVT'AS EVT
FROM
(SELECT SRLNUB, REVCOD, amount
FROM fmtextbl ) ps
PIVOT
(
SUM(amount)
FOR REVCOD IN([TRF],[EVT])
)AS pvt
|
Saturday, March 30, 2013 - 5:59:22 AM - Slick | Back To Top (23085) |
Great post, made something very powerful (and something I've previously overlooked) nice and easy to understand |
Tuesday, March 26, 2013 - 12:04:03 AM - puneet | Back To Top (23011) |
Great Jobs Greg.Thank's again |
Thursday, March 21, 2013 - 3:29:06 AM - vagee | Back To Top (22926) |
@Uvarajan: Read about SQL CASE. It will help you. http://msdn.microsoft.com/en-us/library/ms181765.aspx |
Saturday, March 16, 2013 - 4:28:35 AM - Thush | Back To Top (22824) |
Thanks Greg!. this example is very much clear to understand the usages of SQL cross tab and I was able to build my query based on this. |
Friday, March 8, 2013 - 9:48:20 AM - Kathy | Back To Top (22668) |
Very slick and useful. Thanks, Greg! |
Tuesday, February 26, 2013 - 9:11:25 AM - Uvarajan | Back To Top (22427) |
I have a requirement in which after Pivot, I don’t want the aggregated value, instead I need to replace it by ‘Y’. For Null values it should be replaced by ‘N’. How can I achieve this? Please help me. |
Monday, February 18, 2013 - 3:29:41 PM - Chris F | Back To Top (22245) |
Hmm, I'm not really sure why your version of SQL is acting this way, but if you add the missing "[" bracket in this line (before the "1") it should work:
select EmpId, Ename, 1],[2] from ( |
Friday, February 15, 2013 - 11:44:22 PM - nagaraj | Back To Top (22178) |
Hai sir, I am using,
Microsoft SQL Server Management Studio 10.50.1600.1 Microsoft Analysis Services Client Tools 2008 Microsoft .NET Framework 3.5 Oerationg System 6.1(7600)
|
Friday, February 15, 2013 - 8:59:56 AM - Chris F | Back To Top (22151) |
Can you post your SQL version information? I created this on: Microsoft SQL Server Management Studio 9.00.1399.00 |
Friday, February 15, 2013 - 12:37:05 AM - nagaraj | Back To Top (22133) |
Hai sir , declare @columns varchar(max); While Executing the above query It gives the bellow ouput sir ------------------------------------------ select EmpId, Ename, 1],[2] from (
Please tell me the solution sir. Thanks. |
Thursday, February 14, 2013 - 10:32:11 AM - Chris F | Back To Top (22122) |
@nagaraj -- Try this. Note that it will give NULL values for any day the employee is missing a record. I'm sure there's a way to adjust for this but I don't have time at the moment to figure it out.
|
Thursday, February 14, 2013 - 3:22:27 AM - nagaraj | Back To Top (22114) |
hi sir plz help for this problem
In my table the values are stored as
here i mention only one employe record.There is n number employes there my problem is
empid ename 1 2 3 4 5 6 …….28
here 1 2 3 4 represent dates of the month.
plese kindly provide me the query to get like this.
thanks.
|
Thursday, February 7, 2013 - 11:59:37 AM - Chris F | Back To Top (21988) |
Thanks for your help and the quick reply. This was exactly what I needed. |
Thursday, February 7, 2013 - 11:22:41 AM - Greg Robidoux | Back To Top (21981) |
@Chris F - take a look at this tip written by Aaron Bertrand: http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/
|
Thursday, February 7, 2013 - 11:04:05 AM - Chris F | Back To Top (21979) |
Hi Greg, thanks for the examples, they were helpful. I was wondering if there was an easy way to do a crosstab in SQL server if you don't know all of the possible values for the crosstab column headers, or if there are a large number of them? For example, I have a list of people, and I want to show an aggregate value (let's say sales) for each person, for each month spanning several years. Do I need to manually type out every month value as a column header, or is there a way to let SQL Server figure that out on its own? Access makes this sort of thing very easy, unfortunately the database I'm working with exceeds the limits of what Access can handle. Thanks.
|
Tuesday, February 5, 2013 - 9:55:48 AM - Greg Robidoux | Back To Top (21925) |
@vanapandi - try this query.
SELECT paymentid, |
Monday, February 4, 2013 - 11:56:44 PM - vanapandi | Back To Top (21912) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
This is my table………..
I need result like below table…how to write query for getting this table. |
Saturday, December 15, 2012 - 2:05:53 AM - prashant | Back To Top (20963) |
|
Friday, November 16, 2012 - 5:18:30 PM - Siva | Back To Top (20382) |
This article was good and helped me. Thanks a lot. |
Monday, October 22, 2012 - 6:50:23 AM - MADRI PRATAP | Back To Top (20032) |
Hi Greg, This article is very useful for me. Thanks a lot.
Regards MADRI PRATAP
|
Thursday, October 11, 2012 - 5:07:10 AM - ALEX | Back To Top (19869) |
Hi Greg
Thanks a lot |
Wednesday, September 5, 2012 - 11:57:38 AM - Greg Robidoux | Back To Top (19399) |
@wajira - take a look at this article to see if this answers what you need to do: http://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/ |
Wednesday, September 5, 2012 - 2:32:09 AM - wajira | Back To Top (19393) |
Thanks for this.
but i have an issue when the number of product is unknown and it can be any number. say in this case if we have 100 products how can we get the product name in to columns dynamically rather than hard coding the column name ?
can any one help me |
Friday, June 22, 2012 - 11:25:40 PM - Andy | Back To Top (18182) |
Hi Greg, Thank you for this tutorial. have a question, how do place that to the page using PHP? |
Saturday, March 3, 2012 - 2:48:13 AM - Sunil | Back To Top (16242) |
Hi Greg, This article is very useful for me,i have no words to expressed your article. Thanx a lot !!!
|
Thursday, September 24, 2009 - 5:39:06 AM - swaminathan | Back To Top (4083) |
Hi, This tip is really a useful one.I am having clarification in this.In the example you provided under Products columns there were only two product values so its easy to use that in outer select for naming.My doubt is, suppose, if we are having more records in the column,how can we proceed?.Whether we need to proceed like the same way or is there any better option?.Could you please help me on this. Thanks in advance, Regards, Swami |