Using PIVOT and JOIN Together in SQL Server

• Download source - 792 B

Introduction

When working with cross tab reporting, PIVOT is quite handy. But some time, we may need to use PIVOT and JOIN together. So here with a simple example, we would see how we can use these two things together.

Background

Let's say we have two tables where:

• Employeemaster - Hold the basic information about employees:

  

• Employeesalarydetails - Holds the salary structure details like basics and other others benefits (Transportation allowance, Overtimes, etc.).

  

• Result - Now we want to create a query in a way that the result would be in the form of:

  

Tables And Data

Tables

/*create tables*/
CREATE TABLE Employeemaster(
    Empid BIGINT,
    EmpName VARCHAR(100)
)

CREATE TABLE Employeesalarydetails(
    Empid BIGINT,
    Component VARCHAR(100),
    Amount FLOAT,
)

Data

/*insert datas*/
INSERT 
    INTO Employeemaster
    VALUES (1, 'Emp1'),
    (2, 'Emp2'),
    (3, 'Emp3'),
    (4, 'Emp4')
INSERT 
    INTO Employeesalarydetails
    VALUES(2, 'Basic', 10000),
    (2, 'Hra', 1000),
    (2, 'TA', 750),
    (1, 'Basic', 20000),
    (1, 'Hra', 1000),
    (3, 'Basic', 6700),
    (3, 'Hra', 100),
    (4, 'Basic', 5000),
    (4, 'Hra', 1000)

PIVOT of Employeesalarydetails

The PIVOT of Employeesalarydetails is:

SELECT *
    FROM Employeesalarydetails
    PIVOT(
        SUM(Amount)
            FOR Component
            IN([Basic],[Hra],[TA])
    )AS DtlPivot

To start using PIVOT, check out http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query.

Now to get the required output, we only need to join Employeemaster table and this pivoted result.

JOIN Employeemaster and PIVOT-ed Result

Test live example http://www.sqlfiddle.com/#!3/ad5e6/2.

Using CTE

WITH Dtl
AS
(
    SELECT *
        FROM Employeesalarydetails
        PIVOT(
            SUM(Amount)
                FOR Component
                IN([Basic],[Hra],[TA])        -- pivot for [Basic],[Hra],[TA]
        )AS DtlPivot
)
SELECT Emp.*,
    COALESCE(Dtl.[Basic], 0) AS [Basic],    -- assigning names for [Basic],[Hra],[TA]
    COALESCE(Dtl.[Hra], 0) AS [Hra],
    COALESCE(Dtl.[TA], 0) AS [TA]
    FROM Employeemaster AS Emp
    LEFT JOIN Dtl ON Emp.EmpId = Dtl.EmpId

To know more about CTE, check out http://www.codeproject.com/Articles/275645/CTE-In-SQL-Server.

Without CTE

SELECT Emp.*,
    COALESCE(Dtl.[Basic], 0) AS [Basic],    -- assigning names for [Basic],[Hra],[TA]
    COALESCE(Dtl.[Hra], 0) AS [Hra],
    COALESCE(Dtl.[TA], 0) AS [TA]
    FROM Employeemaster AS Emp    
    LEFT JOIN
    (
        SELECT *
        FROM Employeesalarydetails
        PIVOT(
            SUM(Amount)
                FOR Component
                IN([Basic],[Hra],[TA])        -- pivot for [Basic],[Hra],[TA]
        )AS DtlPivot
    )AS Dtl
    ON Emp.EmpId = Dtl.EmpId

Find the necessary SQL in the attachment.