本資料は Microsoft SQL Server で PIVOT を使った記事となります。
例えば、集計しGROUP BY した結果を横にしたい…縦にしたい…など集計結果の表を縦横に変えたい場合にPIVOTが便利かもしれません。
CASE WHENを使えば、縦横表示は可能ですが…
SELECT @@VERSION Microsoft SQL Server 2019 (RTM-GDR) (KB5021125) - 15.0.2101.7 (X64) Jan 23 2023 13:08:05 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19045: )
以下に記すテーブルとレコードをINSERTし、販売者(person)と商品(product)でPIVOTしてみます。
また、意図しない動作になる例も記します。
本記事の動作を試すには以下のテーブル作成とテストデータをINSERTしてください。
CREATE TABLE sales ( sale_date date ,person varchar(30) ,product varchar(30) ,quantity int );
INSERT INTO sales VALUES ('2023-05-01','佐藤','Linux',1) ,('2023-05-01','佐藤','macOS',2) ,('2023-05-01','佐藤','Windows',3) ,('2023-05-02','佐藤','Linux',4) ,('2023-05-02','佐藤','macOS',5) ,('2023-05-02','佐藤','Windows',6) ,('2023-05-01','鈴木','Linux',10) ,('2023-05-01','鈴木','macOS',20) ,('2023-05-01','鈴木','Windows',30) ,('2023-05-02','鈴木','Linux',40) ,('2023-05-02','鈴木','macOS',50) ,('2023-05-02','鈴木','Windows',60) ,('2023-05-01','高橋','Linux',100) ,('2023-05-01','高橋','macOS',200) ,('2023-05-01','高橋','Windows',300) ,('2023-05-02','高橋','Linux',400) ,('2023-05-02','高橋','macOS',500) ,('2023-05-02','高橋','Windows',600);
本記事では GROUP BY し数量(quantity)の合計値(SUM)を販売者(person)と商品(product)をPIVOTを使って、カラムとして表示します。
以下のようにGROUP BY を使用すると以下のようになります。
SELECT person ,product ,SUM(quantity) AS total FROM sales GROUP BY person ,product ORDER BY person ,product
person | product | total |
高橋 | Linux | 500 |
高橋 | macOS | 700 |
高橋 | Windows | 900 |
佐藤 | Linux | 5 |
佐藤 | macOS | 7 |
佐藤 | Windows | 9 |
鈴木 | Linux | 50 |
鈴木 | macOS | 70 |
鈴木 | Windows | 90 |
personとproductでPIVOTしてみます。
販売者(person)でPIVOTした時のサンプルSQLと結果となります。
SELECT pvt.product ,pvt.高橋 ,pvt.佐藤 ,pvt.鈴木 FROM ( SELECT person, product, quantity FROM sales ) T PIVOT ( SUM(quantity) FOR person IN (高橋,佐藤,鈴木) ) AS pvt
product | 高橋 | 佐藤 | 鈴木 |
Linux | 500 | 5 | 50 |
macOS | 700 | 7 | 70 |
Windows | 900 | 9 | 90 |
販売者(person)がカラムになっているのが確認できます。
商品(product)でPIVOTした時のサンプルSQLと結果となります。
SELECT pvt.person ,pvt.Linux ,pvt.macOS ,pvt.Windows FROM ( SELECT person, product, quantity FROM sales ) T PIVOT ( SUM(quantity) FOR product IN (Linux,macOS,Windows) ) AS pvt
person | Linux | macOS | Windows |
高橋 | 500 | 700 | 900 |
佐藤 | 5 | 7 | 9 |
鈴木 | 50 | 70 | 90 |
上記では、PIVOT対象となる表を以下のSQLで取得しています。
( SELECT person, product, quantity FROM sales ) T
これは、salesテーブルにはsale_date(販売日)があり、以下のようにPIVOTすると、意図した動作になりません。
SELECT pvt.person ,pvt.Linux ,pvt.macOS ,pvt.Windows FROM sales PIVOT ( SUM(quantity) FOR product IN (Linux,macOS,Windows) ) AS pvt
person | Linux | macOS | Windows |
高橋 | 100 | 200 | 300 |
高橋 | 400 | 500 | 600 |
佐藤 | 1 | 2 | 3 |
佐藤 | 4 | 5 | 6 |
鈴木 | 10 | 20 | 30 |
鈴木 | 40 | 50 | 60 |