Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Crosstab queries using PIVOT in SQL Server

MSSQLTips author Greg Robidoux By:   |   Read Comments (50)   |   Related Tips: 1 | 2 | More > Query Optimization
Problem

In SQL Server 2000 there was not a simple way to create cross-tab queries, but a new option first introduced in SQL Server 2005 has made this a bit easier. We took a look at how to create cross-tab queries in SQL Server 2000 in this previous tip and in this tip we will look at this new feature to allow you produce cross-tab results.

Solution

This new feature in SQL Server is PIVOT.  What this allows you to do is to turn query results on their side, so instead of having results listed down like the listing below, you have results listed across.

SalesPerson Product SalesAmount
Bob Pickles $100.00
Sue Oranges $50.00
Bob Pickles $25.00
Bob Oranges $300.00
Sue Oranges $500.00

With a straight query the query results would be listed down, but the ideal solution would be to list the Products across the top for each SalesPerson, such as the following:

SalesPerson Oranges Pickles
Bob $300.00 $125.00
Sue $550.00  

To use PIVOT you need to understand the data and how you want the data displayed.  First you have the data rows, such as SalesPerson and the columns, such as the Products and then the values to display for each cross section.  Here is a simple query that allows us to pull the cross-tab results.

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
  • Review the older methods that were discussed in this prior tip.  These options will still work in all versions of SQL Server and may provide a better solution.
  • Try to write some more complex queries to take advantage of this new option.


Last Update: 10/1/2012


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

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, October 09, 2014 - 5:10:50 AM - joshi987 Read The Tip

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 Read The Tip

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 01, 2014 - 8:33:08 AM - Greg Robidoux Read The Tip

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 01, 2014 - 4:12:51 AM - venkat Read The Tip

 

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 Read The Tip

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

Code    Day     Day2

 JL         1        5

 

Thanks


Tuesday, July 08, 2014 - 9:50:46 AM - Greg Robidoux Read The Tip

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 07, 2014 - 11:56:50 PM - Kristina Read The Tip

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 Read The Tip

excelent queries.

 


Thursday, April 17, 2014 - 1:53:19 AM - Jayprakash Read The Tip

Very good explanation. Thanks for the article.

 


Friday, April 11, 2014 - 4:49:49 PM - Jason Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 09, 2014 - 4:41:27 PM - Greg Robidoux Read The Tip

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 09, 2014 - 3:19:39 PM - Jason Read The Tip

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 05, 2014 - 9:19:13 AM - tanehome Read The Tip

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?

log_id date time reader_id user_id
1 05/03/2014 08:28:32 in_b 1350
2 05/03/2014 16:29:07 out_a 1350
3 05/03/2014 08:30:48 in_a 1375
4 05/03/2014 08:58:40 out_a 1375
5 05/03/2014 08:59:10 in_b 1375
6 05/03/2014 11:08:23 out_a 1375
7 05/03/2014 11:09:13 in_a 1375
8 05/03/2014 11:10:49 out_a 1375
9 05/03/2014 11:56:41 in_a 1375
10 05/03/2014 12:00:04 out_b 1375
11 05/03/2014 12:10:27 in_b 1375
12 05/03/2014 12:10:53 out_a 1375
13 05/03/2014 12:17:50 in_a 1375
14 05/03/2014 12:33:19 in_a 1375
15 05/03/2014 12:36:57 out_a 1375
16 05/03/2014 12:38:17 in_a 1375
17 05/03/2014 12:40:17 out_b 1375
18 05/03/2014 14:12:22 in_b 1375
19 05/03/2014 16:30:35 out_a 1375
20 05/03/2014 08:23:23 in_b 1377
21 05/03/2014 16:23:32 out_b 1377

 

I need the result to be

user_id 01-Mar 02-Mar 03-Mar 04-Mar 05-Mar
1350         8
1359         8
1375         6

Tuesday, February 18, 2014 - 2:15:25 PM - borys tyukin Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

WHAT ADD A COLUMN WITM FUNCTION OTHER?


Thursday, May 23, 2013 - 6:58:07 AM - Prabakaran Read The Tip

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 Read The Tip

I am trying same code to implement in PHP but its giving some error.

 

Table is clientdetails with 3 attributes (clientname,productname,quantity).

 

<?php

mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("HR") or die(mysql_error());
  
$sql=mysql_query("select clientname, 'Software' as s, 'Desktop' as d
FROM (
select clientname,productname,quantity
FROM clientdetails)
ps pivot (Sum(quantity) for productname in ('Software' ,'Desktop'))") or die("". mysql_error());

 

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 Read The Tip

I Have tried the same but not working pls go through my query and let me know what is wrong with my query

SRLNUB    ACCDAT     REGNUB    ROMNUB    GSTNAM            GSTCLF    MANREV    TRNAMT    REVCOD    CNT    amount
3221    20130322 26    402    ESPIRITU ANTONINA    3    TRF    183870    TRF    100    33096.6
3215    20130322 39    407    SCOTT VARGAS SUSAN    3    TRF    183870    EVT    100    33096.6


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


ACCDAT        ROMNUB    REGNUB    GSTNAM            GSTCLF    MANREV    TRF         EVT
20130322    402    26    ESPIRITU ANTONINA           3            TRF         183870    0
20130322    407    39    SCOTT VARGAS SUSAN        3            TRF         0            183870


Monday, April 22, 2013 - 3:18:35 AM - VANI Read The Tip

 

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 Read The Tip

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 Read The Tip

Great Jobs Greg.Thank's again


Thursday, March 21, 2013 - 3:29:06 AM - vagee Read The Tip

@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 Read The Tip

 

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 08, 2013 - 9:48:20 AM - Kathy Read The Tip

Very slick and useful. Thanks, Greg!


Tuesday, February 26, 2013 - 9:11:25 AM - Uvarajan Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

Can you post your SQL version information?  I created this on:

Microsoft SQL Server Management Studio    9.00.1399.00
Microsoft Analysis Services Client Tools    2005.090.1399.00
Microsoft Data Access Components (MDAC)    6.1.7601.17514 (win7sp1_rtm.101119-1850)
Microsoft MSXML    3.0 6.0
Microsoft Internet Explorer    9.0.8112.16421
Microsoft .NET Framework    2.0.50727.5466
Operating System    6.1.7601


Friday, February 15, 2013 - 12:37:05 AM - nagaraj Read The Tip

Hai sir ,

declare @columns varchar(max);
set @columns='';
select @columns=@columns+',['+cast(daynum as varchar(2))+']' from #days;

declare @sql varchar(max);
set @sql = 'select EmpId, Ename, '
+ stuff(@columns,1,2,'') + ' from (
    select EmpId, Ename, DayNumber, Status from Example
) as X
PIVOT (
    min(Status)
    for DayNumber in ('
+ stuff(replace(@columns, ', PVT.[', ','), 1, 1, '')
+ ')
) as PVT order by EmpId;';
print @sql;
execute(@sql)

While Executing the above query It gives the bellow ouput sir

------------------------------------------

select EmpId, Ename, 1],[2] from (
    select EmpId, Ename, DayNumber, Statuss from Example
) as X
PIVOT (
    min(Status)
    for DayNumber in ([1],[2])
) as PVT order by EmpId;
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ']'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'as'.

 

Please tell me the solution sir.

Thanks.


Thursday, February 14, 2013 - 10:32:11 AM - Chris F Read The Tip

@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.


--Example table
--(I've changed your dates from DD/MM/YYYY to MM/DD/YYYY format since my version of SQL can't handle DD/MM/YYYY.)
create table Example (EmpID varchar(10), EName varchar(25), Date datetime, Status varchar(10));
insert into Example select '101', 'abc', '02/01/2013', 'present';
insert into Example select '101', 'abc', '02/02/2013', 'present';
insert into Example select '101', 'abc', '02/03/2013', 'absent';
insert into Example select '102', 'xyz', '02/03/2013', 'present';
insert into Example select '101', 'abc', '02/28/2013', 'present';
insert into Example select '102', 'xyz', '02/28/2013', 'present';

--Need a new column to hold just the day number, since that's what we're pivoting on.
alter table Example add DayNumber varchar(2);
update Example set DayNumber = cast(datepart(dd,Date) as varchar(2));

--Temp table for day numbers, because we need them as varchar but sorted as int.
select DISTINCT datepart(dd,Date) "DayNumber"
into #Days from Example order by 1;

--Build column list, get all crosstab column names.
declare @columns varchar(max);
set @columns = '';
select @columns = @columns + ', [' + cast(DayNumber as varchar(2)) +']' from #Days;

--Build SQL query.
declare @sql varchar(max);
set @sql = 'select EmpID, EName, '
+ stuff(@columns,1,2,'') + ' from (
    select EmpID, EName, DayNumber, Status from Example
) as X
PIVOT (
    min(Status)
    for DayNumber in ('
+ stuff(replace(@columns, ', PVT.[', ','), 1, 1, '')
+ ')
) as PVT order by EmpID;';
print @sql;
execute(@sql);



Thursday, February 14, 2013 - 3:22:27 AM - nagaraj Read The Tip

hi sir plz help for this problem

 

In my table the values are stored as
empid ename date status
————————————————
101 abc 01/02/2013 present
101 abc 02/02/2013 present
101 abc 03/02/2013 absent
102 xyz 03/02/2013 present
and so onnnn
101 abc 28/02/2013 present
102 xyz 28/02/2013 present

 

here i mention only one employe record.There is n number employes there my problem is
how to show table like this

 

empid ename 1 2 3 4 5 6 …….28
————————————————————————
101 abc present present absent…………. presnt
102 xyz present present present………..present

 

here 1 2 3 4 represent dates of the month.

 

plese kindly provide me the query to get like this.
in this above case empid is also varchar

 

thanks.

 


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

Thanks for your help and the quick reply.  This was exactly what I needed.


Thursday, February 07, 2013 - 11:22:41 AM - Greg Robidoux Read The Tip

@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 07, 2013 - 11:04:05 AM - Chris F Read The Tip

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 05, 2013 - 9:55:48 AM - Greg Robidoux Read The Tip

@vanapandi - try this query.

 

SELECT paymentid,
SUM(case when receivedtype = 'dr' then amount
 when receivedtype = 'cr' then -amount end),
reason
FROM #temp a
GROUP BY paymentid, reason


Monday, February 04, 2013 - 11:56:44 PM - vanapandi Read The Tip

PaymentHistoryID

paymentID

Amount

receivedType

Reason

1

11

5000.00

Dr

ASD

2

12

4000.00

Cr

 BG

3

11

8000.00

Cr

 BG

4

11

3000.00

Dr

 SD

5

11

3000.00

Cr

ASD

6

13

50000.00

Cr

ASD

7

13

50000.00

Cr

ASD

8

13

1000.00

Cr

ASD

9

13

500.00

Cr

ASD

This is my table………..

 

 

 

 

 

 

 

 

 

Payment ID

Amount(dr-cr)

Reason

11

5000-3000=2000

ASD

11

0-8000=-8000

BG

12

0-4000=-4000

BG

 I need result like below table…how to write query for getting this table.


Saturday, December 15, 2012 - 2:05:53 AM - prashant Read The Tip

Hi Greg,

This article is very useful for me.


Thanks.


Friday, November 16, 2012 - 5:18:30 PM - Siva Read The Tip

This article was good and helped me. Thanks a lot.


Monday, October 22, 2012 - 6:50:23 AM - MADRI PRATAP Read The Tip

Hi Greg,

  This article is very useful for me. Thanks a lot.

 

Regards

MADRI PRATAP

 


Thursday, October 11, 2012 - 5:07:10 AM - ALEX Read The Tip

Hi Greg

 

Thanks a lot


Wednesday, September 05, 2012 - 11:57:38 AM - Greg Robidoux Read The Tip

@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 05, 2012 - 2:32:09 AM - wajira Read The Tip

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 Read The Tip

Hi Greg,

Thank you for this tutorial. have a question, how do place that to the page using PHP?


Saturday, March 03, 2012 - 2:48:13 AM - Sunil Read The Tip

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 Read The Tip

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




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.