Pivot in SQL SERVER

Hello friends,

Today I will explain you how to use pivot table. It’s very useful.

For example just take  a look on below   statements

 

DECLARE @tblTest AS TABLE (strName        VARCHAR(50),

                           fltTotalAmount DECIMAL(10,3),

                           intMonth       TINYINT,

                           intYear        INT)

 

INSERT INTO @tblTest (strName, fltTotalAmount, intMonth, intYear)

VALUES (‘RAJAT’, ‘10000’,1,2006)

INSERT INTO @tblTest (strName, fltTotalAmount, intMonth, intYear)

VALUES (‘RAJAT’, ‘15000’,2,2006)

INSERT INTO @tblTest (strName, fltTotalAmount, intMonth, intYear)

VALUES (‘RAJAT’, ‘16000’,3,2006)

INSERT INTO @tblTest (strName, fltTotalAmount, intMonth, intYear)

VALUES (‘RAJAT’, ‘10700’,1,2007)

INSERT INTO @tblTest (strName, fltTotalAmount, intMonth, intYear)

VALUES (‘RAJAT’, ‘16000’,2,2007)

 

Now suppose if you want sql result in following format

Name	Year	Jan	Feb	march
RAJAT	2006	10000.000	15000.000	16000.000
RAJAT	2007	10700.000	16000.000	NULL

  Then what you have to do?

You have to do following thing just take a look below statements.

 

SELECT strName,

       intYear,

       [1] As Jan,

       [2] AS Feb,

       [3] As mar

FROM ( SELECT strName,

       intYear,

       fltTotalAmount,

       intMonth

       FROM @tblTest )p

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

 

Now when you execute it it will give you desire result.

 

I hope you got the idea how to use pivot table.

If you have any kind of problem or confusion in the above statement feel free to drop a comment.

Thanks

Regards

Rajat Jaiswal