Unpivot This!

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:

 

 

 

  1. CREATE TABLE Sales (
  2.     Id INT,
  3.     Total,
  4.     Category,
  5.     ProductId,
  6.     CouponId,
  7.     DateTime
  8. )

 

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.

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

  1. SELECT
  2.     SUM(Total) AS ‘Daily Total’,
  3.     COUNT(Id) AS ‘Number of Sales’,
  4.     SUM(CASE WHEN ProductId = @specificProduct THEN Total) ‘Specific Product Sales’
  5. INTO myTmpTable
  6. FROM Sales
  7. 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.

  1. INSERT INTO DailyRecap
  2. SELECT
  3.     Key,
  4.     Value
  5. FROM
  6. (
  7.     SELECT
  8.         Key,
  9.         Value
  10.     FROM myTmpTable
  11.     UNPIVOT
  12.     (
  13.         Value for Key IN (‘Daily Total’, ‘Number of Sales’, ‘Specific Product Sales’)
  14.     ) unpiv
  15. ) source

 

NOTE: This will not work unless the key and value are of the same data type!

Leave a Reply

Your email address will not be published. Required fields are marked *

+ 44 = 45

Post Navigation