PIVOT in SQL is very power full technique to transform row level data into column level.
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.