SQL Standard

SQL Unpivot

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:

    <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:

    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

    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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list