UNPIVOT
UNPIVOT is a relational operator in SQL that transforms columns into rows. This operator is useful when you have column-oriented data that you want to convert into a row-oriented format, helping in normalizing the denormalized tables.
Example
CREATE TABLE #Temp( Item VARCHAR(50), Jan INT, Feb INT, Mar INT)INSERT INTO #TempVALUES('Apples', 10, 20, 30),('Oranges', 40, 50, 60)
SELECT Item, Month, SalesFROM( SELECT Item, Jan, Feb, Mar FROM #Temp) as pUNPIVOT( Sales FOR Month IN (Jan, Feb, Mar)) AS unpvtDROP TABLE #Temp
Output
Item | Month | Sales---------|-------|-------Apples | Jan | 10Apples | Feb | 20Apples | Mar | 30Oranges | Jan | 40Oranges | Feb | 50Oranges | Mar | 60
Explanation
This example first creates a temporary table, #Temp
, with an item column and sales data columns for each of three months - January, February, and March. Then, UNPIVOT
is used to convert these months’ columns into row values under the ‘Month’ column, along with the corresponding sales values under the ‘Sales’ column for each item. This turns the data from a ‘wide’ format to a ‘long’ format. At last, the temporary table #Temp
is dropped.
Example
-- Creating an example table named ordersCREATE TABLE orders(order_id NUMBER, Monday NUMBER, Tuesday NUMBER, Wednesday NUMBER);-- Inserting sample dataINSERT INTO orders VALUES (1, 10, 15, 20);INSERT INTO orders VALUES (2, 25, 30, 35);
-- Using UNPIVOT to transform columns into rowsSELECT * FROM ordersUNPIVOT( Sales FOR Day_of_week IN (Monday, Tuesday, Wednesday));
Output
ORDER_ID | DAY_OF_WEEK | SALES------------------------------ 1 | MONDAY | 10 1 | TUESDAY | 15 1 | WEDNESDAY | 20 2 | MONDAY | 25 2 | TUESDAY | 30 2 | WEDNESDAY | 35
Explanation
In this example, the UNPIVOT
operation is used to transform column data from the ‘orders’ table into rows. Initially, the table contains energy sales for Monday, Tuesday, and Wednesday as separate columns for each order. The UNPIVOT
operation is used to create a new form of the table where each day of the week becomes a separate row linked to the order_id, thus increasing granularity of the data. The ‘Day_of_week’ and ‘Sales’ columns in the resulting table are created by the UNPIVOT
operation.