SQL Reference - UNPIVOT

Example

CREATE TABLE #Temp
(
    Item VARCHAR(50),
    Jan INT,
    Feb INT,
    Mar INT
)
INSERT INTO #Temp
VALUES
('Apples', 10, 20, 30),
('Oranges', 40, 50, 60)

SELECT Item, Month, Sales
FROM
(
  SELECT Item, Jan, Feb, Mar
  FROM #Temp
) as p
UNPIVOT
(
  Sales FOR Month IN (Jan, Feb, Mar)
) AS unpvt
DROP TABLE #Temp

Output

Item     | Month | Sales
---------|-------|-------
Apples   | Jan   | 10
Apples   | Feb   | 20
Apples   | Mar   | 30
Oranges  | Jan   | 40
Oranges  | Feb   | 50
Oranges  | 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 orders
CREATE TABLE orders(order_id NUMBER, Monday NUMBER, Tuesday NUMBER, Wednesday NUMBER);
-- Inserting sample data
INSERT INTO orders VALUES (1, 10, 15, 20);
INSERT INTO orders VALUES (2, 25, 30, 35);

-- Using UNPIVOT to transform columns into rows
SELECT * FROM orders
UNPIVOT
(
  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.