UNPIVOT Clause
The UNPIVOT
clause turns rows of a table-valued expression into column values.
Syntax
SELECT <unpivoted columns>
FROM
(<a SELECT query that produces the data>)
AS <alias for the source query>
UNPIVOT
(
<a column that contains the values of the columns that will become values instead of headers>
FOR
[<a column that contains the values that were headers>]
IN ( [first column header], [second column header],
... [last column header])
) AS <an alias for the unpivoted table>
<optional ORDER BY clause>;;
Example
-- Create a table and insert some data
CREATE TABLE pvt (VendorID INTEGER, Emp1 INTEGER, Emp2 INTEGER, Emp3 INTEGER, Emp4 INTEGER, Emp5 INTEGER);;
INSERT INTO pvt VALUES (1, 4, 3, 5, 4, 4);;
-- Unpivot the table
SELECT VendorID, Employee, Orders
FROM (
SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt
) p
UNPIVOT (
Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5)
) AS unpvt;;