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