Converting Row to Column use Pivot feature TIP #62

 

Problem:  Sometimes we may require to convert row data to column. we require pivot view of data.

Solution:  Lets understand this by an example below

I am using a table variable which have few columns like  Employee, Amount, Month and year. Lets define and create some sample data here.

DECLARE @tblEmployee AS TABLE (Employee        VARCHAR(50),
                           Amount DECIMAL(10,3),
                           intMonth       TINYINT,
                           intYear        INT)

INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '10000',1,2013)
INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '15000',2,2013)
INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '16000',3,2013)
INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '10700',1,2014)
INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '16000',2,2014)

Now just cross check the data how it look

SELECT * FROM @tblEmployee

Now we want result something like as shown in below figure

Now to achieve this we wrote following pivot syntax

SELECT Employee,
       intYear,
       [1] As Jan,
       [2] AS Feb,
       [3] As mar
FROM ( SELECT Employee,
       intYear,
       Amount,
       intMonth
       FROM @tblEmployee )p

PIVOT (SUM (Amount) FOR intMonth IN ([1],[2],[3]))Q

 

I hope this may help you somewhere.

In coming tips we would share how to create dynamic Pivot and  UNPIVOT.

Thanks & Enjoy !!!

RJ!!