SQL/SQLServerでPIVOTを使ったサンプル のバックアップ(No.1)

[ トップ ]   [ 新規 | 一覧 | 検索 | 最終更新 | ヘルプ ]

• バックアップ一覧
• 差分 を表示
• 現在との差分 を表示
• ソース を表示
• SQL/SQLServerでPIVOTを使ったサンプル へ行く。
  • 1 (2023-05-01 (月) 17:48:50)

---

• Prev
• Next
• SQL

---

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

結果 †

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

結果 †

失敗例 †

上記では、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

  販売日が複数あるので意図した動作になっていなのが確認できます。

     

Site admin: IT SUPPORT SAKURA

PukiWiki 1.5.2 © 2001-2019 PukiWiki Development Team. Powered by PHP 7.4.33. HTML convert time: 0.011 sec.