UNPIVOT Clause

UNPIVOT句は、テーブル値式の行を列値に変えます。

Syntax

SELECT <'unpivoted columns'>
FROM
    (
    <'SELECT query that produces the data'>
) AS <'alias for the source query'>
UNPIVOT (
    <'a column containing the values of the columns that will become values instead of headers'>
 FOR <'a column containing 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 data
 
CREATE TABLE testTable (AuthorID INTEGER, Book1 INTEGER, Book2 INTEGER, Book3 INTEGER);;
INSERT INTO testTable VALUES (1, 4, 3, 5, 4, 4);;
 
-- Unpivot the table
 
SELECT AuthorID, Book, Orders
FROM (
 SELECT AuthorID, Book1, Book2, Book3
 FROM testTable
 ) p 
UNPIVOT (
    Orders FOR Book IN (Book1, Book2, Book3)
) AS unpivotedTable;;