このエントリーをはてなブックマークに追加


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
  • 結果
    personproducttotal
    高橋Linux500
    高橋macOS700
    高橋Windows900
    佐藤Linux5
    佐藤macOS7
    佐藤Windows9
    鈴木Linux50
    鈴木macOS70
    鈴木Windows90

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高橋佐藤鈴木
Linux500550
macOS700770
Windows900990

販売者(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

結果

personLinuxmacOSWindows
高橋500700900
佐藤579
鈴木507090

失敗例

上記では、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
  • 結果
    personLinuxmacOSWindows
    高橋100200300
    高橋400500600
    佐藤123
    佐藤456
    鈴木102030
    鈴木405060
    販売日が複数あるので意図した動作になっていなのが確認できます。

以上、SQL ServerでPIVOTを使った記事でした。


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2023-05-01 (月) 17:49:38