# Crosstab queries using PIVOT in SQL Server

By:   |   Comments (68)   |   Related: 1 | 2 | > 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.

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

View all my tips

 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  select StudentName,EC1,EC2,EC3,EC4,Totalfrom(select StudentName,Subject,SMarks from Student ) as SourceTablePIVOT(sum(SMarks) for Subject in(EC1,EC2,EC3,EC4,Total))as pivotTable   70705323ASPDKDSPPK33576363334663 6323837353NULLNULLNULLNULL     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  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) psPIVOT ( sum(Total_Rsv) FOR data_src IN ( [PPSSET], [TXSSET]) ) AS pvt

 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

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

 DESTFLD AFS CLCS CPI ForeignOfficeDeposits HEQ ILN InvSec Mansec_Swaps Manualx Manual_SBO QRMLiab TDA AMORT_DATE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL AMORT_TYPE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL AVGBAL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL AVGBALANCE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL BALLN_DATD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL BOOK_VALUE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

 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)go insert into test (pubno, memberno) values (1,5);insert into test (pubno, memberno) values (3,5);insert into test (pubno, memberno) values (6,5);insert into test (pubno, memberno) values (2,1);insert into test (pubno, memberno) values (5,7);insert into test (pubno, memberno) values (8,4);insert into test (pubno, memberno) values (120,9);insert into test (pubno, memberno) values (80,120);insert into test (pubno, memberno) values (50,60);insert into test (pubno, memberno) values (60,70);go My expected result would be GrpID member----- ------1  11  21  31  51  61  72  42  83  93  1203  804  50 4  604  70could could you please kindly help me with much efficient method? I have more than 20 million records to group like this Many thanks Menaka

 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, 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 thisCode    Day     Day2  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.

 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.

hi all,

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

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

 Monday, April 22, 2013 - 3:24:22 AM - vani Back To Top (23465) `I Have tried the same but not working pls go through my query and let me know what is wrong with my querySRLNUB    ACCDAT     REGNUB    ROMNUB    GSTNAM            GSTCLF    MANREV    TRNAMT    REVCOD    CNT    amount 3221    20130322 26    402    ESPIRITU ANTONINA    3    TRF    183870    TRF    100    33096.63215    20130322 39    407    SCOTT VARGAS SUSAN    3    TRF    183870    EVT    100    33096.6And the query is  as follows  SELECT SRLNUB, 'Trf' AS TRF ,'EVT' AS EVT  FROM (SELECT SRLNUB, REVCOD, amountFROM fmtextbl ) psPIVOT(SUM (amount)FOR REVCOD IN ([TRF],[EVT]) ) AS pvtACCDAT        ROMNUB    REGNUB    GSTNAM            GSTCLF    MANREV    TRF         EVT 20130322    402    26    ESPIRITU ANTONINA           3            TRF         183870    020130322    407    39    SCOTT VARGAS SUSAN        3            TRF         0            183870`

 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

 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.00Microsoft Analysis Services Client Tools    2005.090.1399.00Microsoft Data Access Components (MDAC)    6.1.7601.17514 (win7sp1_rtm.101119-1850)Microsoft MSXML    3.0 6.0 Microsoft Internet Explorer    9.0.8112.16421Microsoft .NET Framework    2.0.50727.5466Operating System    6.1.7601

 Friday, February 15, 2013 - 12:37:05 AM - nagaraj Back To Top (22133) 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 XPIVOT (    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 XPIVOT (    min(Status)    for DayNumber in ([1],[2])) as PVT order by EmpId;Msg 102, Level 15, State 1, Line 1Incorrect syntax near ']'.Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'as'.   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. --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 XPIVOT (    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 Back To Top (22114) 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 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, SUM(case when receivedtype = 'dr' then amount  when receivedtype = 'cr' then -amount end),reasonFROM #temp aGROUP BY paymentid, reason

 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.