SQL Code: Crosstab queries with inner join 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

inner join

   Select  EpfNo,  Sum(PaySalary) As  Total

   From  EmpSalary

   Group By EpfNo

)  DB2

 PIVOT
)  PS
  SUM (PaySalary)
  FOR Month IN
  ( [JAN], [FAB])
) AS pvt

After Run CrossTab Query Using PIVOT

EpfNo        Name         BirthDay             JAN           FAB          Total

100     Jone     07/01/1980   25500    20000   45500

101     Deyon    08/20/1985   13500    26800   40300

102     Nimal    10/01/2000   25500    13000   38500

103     Deyon    11/25/1975   20500    20000   40500

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

SQL Code