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.
The operator follows the syntax as shown below:
<aggregation function>(<COLUMN being aggregated>)
[<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>;
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:
-- 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:
Let us now perform an upvitot operation on the table above. The query is as illustrated below:
SELECT product_name, product1, product2 FROM unpiv
) AS tb1
Quantity FOR product_name IN (product1, product2)
) AS unpivt;
The query above should convert the columns into rows using the unpivot operator.
This article provides the fundamentals of using the unpivot operator in SQL. Check the documentation for your database engine to learn more.