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

 

 

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s