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.

SQL Query as below:

Select * from CustomerData

SQL PIVOT Query:

--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

PIVOT Result:

After execution, query result set 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.

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.