Crosstab queries using PIVOT in SQL Server

TABLE1 : EmpMaster 

EpfNo           Name              BathDay

100     Jone     07/01/1980  

101     Deyon    08/20/1985

102     Nimal    10/01/2000

103     Deyon    11/25/1975

TABLE1 : EmpSalary  

EpfNo        Month         PaySalary
100      Jan      25000

100      Jan        500

101      Jan      13000

101      Jan        500 

102      Jan      25000

102      Jan        500 

103      Jan      20000

100      Fab      20000

101      Fab      26800
102      Fab      13000

103      Fab      20000

SQL Query:

SELECT EpfNo, Name, BathDay, Jan, Fab
FROM 
(SELECT EpfNo, Name, PaySalary
FROM EmpMaster) DB1
PIVOT
(
SUM (PaySalary)
FOR Month IN
( [JAN], [FAB])
) AS pvt

After Run CrossTab Query Using PIVOT 

EpfNo        Name         BirthDay             JAN           FAB             

100     Jone     07/01/1980   25500    20000    

101     Deyon    08/20/1985   13500    26800   

102     Nimal    10/01/2000   25500    13000    

103     Deyon    11/25/1975   20500    20000   

How does this work?
There are three pieces  in order to construct the query.

• (1) The SELECT statement

  • SELECT EpfNo, Name, BathDay, Jan, Fab

  • This portion of the query selects the three columns for the final result set (Epfno, Name, Birthday, Jan, Fab)
  
  
• (2) The query that pulls the raw data to be prepared
  • (SELECT EpfNo, Name, PaySalary FROM EmpMaster) ps
  • This query pulls all the rows of data that we need to create the cross-tab results.  The (DB1) 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 (PaySalary)FOR Month IN( [JAN], [FAB])) 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.

On Error found in running this query

Change the database compatibility level using following command.

For SQL Server 2005:
EXEC sp_dbcmptlevel 'DatabaseName', 90

For SQL Server 2008:
EXEC sp_dbcmptlevel 'DatabaseName', 100