Microsoft SQL ServerでPIVOTを使ったサンプル †
本資料は 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: )
PIVOTを試す前に †
以下に記すテーブルとレコードを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 した場合 †
本記事では GROUP BY し数量(quantity)の合計値(SUM)を販売者(person)と商品(product)をPIVOTを使って、カラムとして表示します。
以下のようにGROUP BY を使用すると以下のようになります。
- SQL
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 |
PIVOTを試す †
personとproductでPIVOTしてみます。
販売者(person)でPIVOT †
販売者(person)でPIVOTした時のサンプルSQLと結果となります。
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 †
商品(product)でPIVOTした時のサンプルSQLと結果となります。
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すると、意図した動作になりません。
- SQL
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 |
販売日が複数あるので意図した動作になっていなのが確認できます。
以上、SQL ServerでPIVOTを使った記事でした。
|