The SQL unpivot operator allows you to convert columns into rows. Let us use this tutorial to illustrate how to use the unpivot operator in SQL.
Operator Syntax
The operator follows the syntax as shown below:
SELECT (COLUMNS)
FROM (TABLE_NAME)
UNPIVOT
(
<aggregation function>(<COLUMN being aggregated>)
FOR
[<COLUMN that contains the VALUES that will become COLUMN headers>]
IN ( [FIRST pivoted COLUMN], [SECOND pivoted COLUMN],
... [LAST pivoted COLUMN])
) AS <alias FOR the pivot table>
<optional ORDER BY clause>;
FROM (TABLE_NAME)
UNPIVOT
(
<aggregation function>(<COLUMN being aggregated>)
FOR
[<COLUMN that contains the VALUES that will become COLUMN headers>]
IN ( [FIRST pivoted COLUMN], [SECOND pivoted COLUMN],
... [LAST pivoted COLUMN])
) AS <alias FOR the pivot table>
<optional ORDER BY clause>;
nm
Let us understand how to use the unpivot operator by using a practical example.
SQL Unpivot Practical Example
Let us start by creating a sample table as shown:
CREATE TABLE unpiv(
id serial,
product_name VARCHAR(50),
price INT,
quantity INT
);
-- insert sample data
INSERT INTO unpiv(product_name, price, quantity)
VALUES ('product1', 9.99, 10),
('product2', 11.90, 2),
('product3', 114.00, 3),
('product4', 33.2, 4),
('product5', 221.20, 5);
id serial,
product_name VARCHAR(50),
price INT,
quantity INT
);
-- insert sample data
INSERT INTO unpiv(product_name, price, quantity)
VALUES ('product1', 9.99, 10),
('product2', 11.90, 2),
('product3', 114.00, 3),
('product4', 33.2, 4),
('product5', 221.20, 5);
Let us get the table data as:
SELECT * FROM unpiv;
Let us now perform an upvitot operation on the table above. The query is as illustrated below:
SELECT product_name, price, quantity
FROM
(
SELECT product_name, product1, product2 FROM unpiv
) AS tb1
unpivot
(
Quantity FOR product_name IN (product1, product2)
) AS unpivt;
FROM
(
SELECT product_name, product1, product2 FROM unpiv
) AS tb1
unpivot
(
Quantity FOR product_name IN (product1, product2)
) AS unpivt;
The query above should convert the columns into rows using the unpivot operator.
Conclusion
This article provides the fundamentals of using the unpivot operator in SQL. Check the documentation for your database engine to learn more.
