SQL fry: PIVOT and UNPIVOT
What is PIVOT?
In our day-to-day SQL server coding, PIVOT and UNPIVOT are not very familiar words but still holds good if we understand them correctly and know when to use it.Web Definition of PIVOT says “The central point, pin, or shaft on which a mechanism turns or oscillates.”
We just have to concentrate on two points of the above definition- a central point and a mechanism. These two points are required to define PIVOT in SQL server.
In SQL server, we have a Table like Transactions which records Customer,UnitsBought and AmountPaid. This table with sample data looks like
When we are going to pivot this table, we require a ‘central point’ which for example we take as SUM(UnitsBought) and ‘a mechnaism’ which is the static values of Amount paid [10,100,150,500]. Now we just have to turn this mechanism on a central point. Lets see the query to do that and how the result looks like
SELECT * FROM transactions PIVOT (SUM(UnitsBought) For AmountPaid IN (,,,)) AS EachAmountPaid
And the result of PIVOT operation will be
When we say static values of Amount paid [10,100,150,500], then we mean that the amount paid should not vary for each and every row ,as for that condition we will have to write a dynamic pivot query.
We stick with this simple example to grasp the concept. Also, we will keep the post short and cover UNPIVOT in subsequent post.