PIVOT tables are not commonly used in SQL Queries or Procedures. UNPIVOT tables are even more uncommon. But a topic that I would like to discuss today, because I found a cool use for UNPIVOT and feel like sharing with everyone.
We have all been in a position where we were required to reconcile values in one table and insert them into another. This is how I ran into a good use for UNPIVOT.
Let’s assume that we have a transaction table that holds all different types of sales. For simple reasons let us do something like:
- CREATE TABLE Sales (
- Id INT,
Now that we know the table that we are working with we may need to reconcile some specific information into a daily recap table every evening.
- INSERT INTO DailyRecap SELECT ‘Daily Total’, SUM(Total) FROM Sales WHERE DateTime BETWEEN(@startOfDay, @endOfDay);
Now imagine if you have to do that for several categories, coupon types, and product categories. A simple solution is to select what you are needing up front.
- SUM(Total) AS ‘Daily Total’,
- COUNT(Id) AS ‘Number of Sales’,
- SUM(CASE WHEN ProductId = @specificProduct THEN Total) ‘Specific Product Sales’
- INTO myTmpTable
- FROM Sales
- WHERE DateTime BETWEEN (@startOfDay AND @endOfDay)
The above query gives us a temp table called myTmpTable which has one row with three columns. In order to INSERT INTO SELECT we need to transform myTmpTable into a key value pairing illustrated in the insert statement above.
- INSERT INTO DailyRecap
- FROM myTmpTable
- Value for Key IN (‘Daily Total’, ‘Number of Sales’, ‘Specific Product Sales’)
- ) unpiv
- ) source
NOTE: This will not work unless the key and value are of the same data type!