Pivoting data is a common practice while analyzing data. The basic idea is to take a single column’s data and pivot the unique values into separate columns. In this post I am going to show a simple example of how we can implement a PIVOT statement in a query. In a future post I will discuss how we can create a dynamic PIVOT based on the functionality below. SQL Server 2005 introduced functionality to help with pivoting data. You can PIVOT data using the following syntax
SELECT [AnyColumn], [PivotCol1],[PivotCol2],...,[PivotColN] FROM ( SELECT [AnyColumn] , [DataColumn] , [AggregateColumn] FROM SomeTable ) P PIVOT ( SUM(AggregateColumn) FOR DataColumn IN ([PivotCol1],[PivotCol2],...,[PivotColN]) ) AS PVT |
So, let’s take a look at a bit of data. The temp table #CustomerProducts below stores a snippet of transaction data. This data is describing the number of candy bars ordered per transaction by a given customer, over time.
CREATE TABLE #CustomerProducts ( CustomerID int , ProductName nvarchar(50) , Quantity int) INSERT INTO #CustomerProducts (CustomerID, ProductName, Quantity) VALUES (1, N'Snickers', 1), (1, N'Butterfinger', 1), (1, N'Rolo', 2), (1, N'Snickers', 1), (1, N'Butterfinger', 1), (2, N'Rolo', 2); |
| CustomerID | ProductName | Quantity |
|---|---|---|
| 1 | Snickers | 1 |
| 1 | Butterfinger | 1 |
| 1 | Rolo | 2 |
| 1 | Snickers | 1 |
| 1 | Butterfinger | 1 |
| 2 | Rolo | 2 |
However, we would like to see how many candy bars each customer has ordered total, as described in the table below:
| CustomerID | Butterfinger | Rolo | Snickers |
|---|---|---|---|
| 1 | 2 | 2 | 2 |
| 2 | NULL | 2 | NULL |
So how do we accomplish this task? Simple, we will implement a PIVOT query…
Before we can develop the PIVOT query we need to determine the PIVOT columns. These columns will come from the data located in the product name column. According the the table above we want to use the following columns: Butterfinger, Rolo, and Snickers. Next we will want to determine the column that we are going to aggregate. In our example, we will use the Quantity column and we will summarize the results. With this information we can plug it into the a PIVOT query and get the query listed below.
CREATE TABLE #CustomerProducts ( CustomerID int , ProductName nvarchar(50) , Quantity int) INSERT INTO #CustomerProducts (CustomerID, ProductName, Quantity) VALUES (1, N'Snickers', 1), (1, N'Butterfinger', 1), (1, N'Rolo', 2), (1, N'Snickers', 1), (1, N'Butterfinger', 1), (2, N'Rolo', 2); SELECT [CustomerID] , [Butterfinger],[Rolo],[Snickers] FROM ( SELECT [CustomerID] , [ProductName] , [Quantity] FROM #CustomerProducts ) P PIVOT ( SUM(Quantity) FOR P.ProductName IN ([Butterfinger],[Rolo],[Snickers]) ) AS PVT IF OBJECT_ID('tempdb..#CustomerProducts') IS NOT NULL DROP TABLE #CustomerProducts |
So, whats next? Well, what if another candy bar type is sold? What if 10 different types are sold? We can continue to update the PIVOT query above, or we can use a Dynamic PIVOT Query. In another post I will describe how to create a Dynamic PIVOT Query.
