Use of PIVOT in SQL

Sandeepraturi
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 725 red flag

PIVOT in SQL is very power full technique to transform row level data into column level.


 Download source code for Use of PIVOT in SQL

Introduction

Here, we will learn how to use PIVOT in SQL. It is very useful technique to transform or display data Row level to Column.

Description with Query

Suppose, we are working on large data and we want to analyze the data year wise and month wise for reporting purpose in that case PIVOT will help us to make it easy.
Help of PIVOT we can transform or display data row level to column level. It is also useful for to make multidimensional reports.

Query as Below with screen shot:-

Select * from CustomerData 
 --Get monthwise customer data
SELECT *

FROM (

SELECT name, mobileno, left(datename(month,dtDate),3)as [month], amount
FROM CustomerData 

) as s
PIVOT
(
SUM(amount) FOR [month] IN (jan, feb, mar, apr,may, jun, 
jul, aug, sep, oct, nov, dec)

)AS pvt


After execution of query output  screen as above. You can pass year in query then you can get particular year month wise data.

Conclusion

  • Pivot help us to transform or display data row to column level.
  • It help us to generate Multi-Dimensional report.
  • We can generate Month & year wise summary of large data.


Page copy protected against web site content infringement by Copyscape

About the Author

Sandeepraturi
Full Name: Sandeep Raturi
Member Level: Starter
Member Status: Member
Member Since: 2/28/2012 10:43:45 AM
Country: India
Thanks & Regards Sandeep Raturi(Sr. software engineer) Email: s.raturi89@gmail.com
http://www.dotnetfunda.com/profile/sandeepraturi.aspx
Senior Software Engineer

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)