SQLでデータを自由自在に整形！PIVOT、UNPIVOT、CASE式を使いこなすテクニック集

2024-05-22

SQLで列と行を転置する方法：わかりやすい解説

SQLで列と行を転置することは、データ分析や可視化において役立つ操作です。様々な方法がありますが、ここではPIVOTとUNPIVOTという2つの基本的な方法と、CASE式を用いた応用例について、SQL Serverを例にわかりやすく解説します。

PIVOT を使った転置

PIVOTは、行方向のデータを列方向に回転させる操作です。集計と転置を同時に実行できます。

例：売上データの月別集計と転置

SELECT
  [社員名],
  SUM(CASE WHEN [月] = '1月' THEN [売上] ELSE 0 END) AS '1月売上',
  SUM(CASE WHEN [月] = '2月' THEN [売上] ELSE 0 END) AS '2月売上',
  ...
  SUM(CASE WHEN [月] = '12月' THEN [売上] ELSE 0 END) AS '12月売上'
FROM [売上データ]
GROUP BY [社員名]
PIVOT
(
  SUM([売上])
  FOR [月] IN ('1月', '2月', ..., '12月')
)
ORDER BY [社員名];

解説

このクエリは、売上データテーブルから社員名と各月の売上を集計し、列方向に回転させています。
CASE式を使って、各月の売上を個別の列として抽出しています。
PIVOT句で、集計列と転置する列を指定しています。
GROUP BY句で、集計対象となる列を指定しています。

例：転置された売上データの列を元に戻す

SELECT
  [社員名],
  [月],
  [売上]
FROM
(
  SELECT
    [社員名],
    [月売上] AS [月],
    [売上]
  FROM [転置された売上データ]
) AS unpivot_data;

このクエリは、PIVOTで転置された売上データを行方向に戻しています。
UNPIVOT句は、SQL Serverには存在しないため、サブクエリを使って実現しています。

CASE式を使った応用

CASE式は、条件に応じて列名を動的に生成することができます。PIVOTやUNPIVOTと組み合わせて、より柔軟な転置操作を実現できます。

例：商品カテゴリー別の売上データの転置

SELECT
  [商品カテゴリー],
  [月],
  SUM([売上]) AS [売上]
FROM [売上データ]
GROUP BY [商品カテゴリー], [月]
PIVOT
(
  SUM([売上])
  FOR [商品名] IN
  (
    SELECT DISTINCT [商品名]
    FROM [売上データ]
  )
)
AS pivot_data
ORDER BY [商品カテゴリー], [月];

このクエリは、商品カテゴリーと月別に売上を集計し、商品名ごとに列を生成して転置しています。
IN句で、PIVOTする列の値を動的に生成しています。
CASE式を使って、列名を商品名にしています。

PIVOTとUNPIVOTは、SQLで列と行を転置する基本的な方法です。CASE式と組み合わせることで、より柔軟な転置操作を実現できます。

これらの方法は、売上データの分析やレポート作成など、様々な場面で役立ちます。

上記以外にも、データベースの種類やバージョンによって、転置操作を行う方法は様々です。具体的な実装方法については、使用しているデータベースのドキュメントを参照してください。

PIVOT を使った転置

-- 売上データテーブルを作成
CREATE TABLE [売上データ] (
  [社員名] VARCHAR(50),
  [月] VARCHAR(20),
  [売上] DECIMAL(10,2)
);

-- 売上データを入力
INSERT INTO [売上データ] VALUES
('田中', '1月', 1000),
('田中', '2月', 1500),
('田中', '3月', 2000),
('佐藤', '1月', 800),
('佐藤', '2月', 1200),
('佐藤', '3月', 1800);

-- 売上データを月別集計し、転置
SELECT
  [社員名],
  SUM(CASE WHEN [月] = '1月' THEN [売上] ELSE 0 END) AS '1月売上',
  SUM(CASE WHEN [月] = '2月' THEN [売上] ELSE 0 END) AS '2月売上',
  SUM(CASE WHEN [月] = '3月' THEN [売上] ELSE 0 END) AS '3月売上'
FROM [売上データ]
GROUP BY [社員名]
PIVOT
(
  SUM([売上])
  FOR [月] IN ('1月', '2月', '3月')
)
ORDER BY [社員名];

出力

社員名     1月売上 2月売上 3月売上
田中       1000    1500    2000
佐藤        800    1200    1800

UNPIVOT を使った転置

-- 転置された売上データテーブルを作成
CREATE TABLE [転置された売上データ] (
  [社員名] VARCHAR(50),
  [月] VARCHAR(20),
  [売上] DECIMAL(10,2)
);

-- 転置された売上データを入力
INSERT INTO [転置された売上データ] VALUES
('田中', '1月', 1000),
('田中', '2月', 1500),
('田中', '3月', 2000),
('佐藤', '1月', 800),
('佐藤', '2月', 1200),
('佐藤', '3月', 1800);

-- 転置された売上データを列方向に戻す
SELECT
  [社員名],
  [月],
  [売上]
FROM
(
  SELECT
    [社員名],
    [月売上] AS [月],
    [売上]
  FROM [転置された売上データ]
) AS unpivot_data;

社員名     月       売上
田中       1月     1000
田中       2月     1500
田中       3月     2000
佐藤       1月      800
佐藤       2月     1200
佐藤       3月     1800

CASE式を使った応用

-- 商品カテゴリー別の売上データテーブルを作成
CREATE TABLE [売上データ] (
  [商品カテゴリー] VARCHAR(50),
  [商品名] VARCHAR(50),
  [月] VARCHAR(20),
  [売上] DECIMAL(10,2)
);

-- 商品カテゴリー別の売上データを入力
INSERT INTO [売上データ] VALUES
('衣料品', 'Tシャツ', '1月', 500),
('衣料品', 'Tシャツ', '2月', 700),
('衣料品', 'Tシャツ', '3月', 900),
('衣料品', 'ジーンズ', '1月', 800),
('衣料品', 'ジーンズ', '2月', 1100),
('衣料品', 'ジーンズ', '3月', 1400),
('食品', 'パン', '1月', 300),
('食品', 'パン', '2月', 400),
('食品', 'パン', '3月', 500),
('食品', '牛乳', '1月', 200),
('食品', '牛乳

SQLで列と行を転置するその他の方法

サブクエリ

サブクエリを使って、転置処理を複数回のクエリで実行する方法です。PIVOTやUNPIVOTが使えないデータベースや、柔軟な転置処理が必要な場合に有効です。

利点

柔軟性が高い
PIVOTやUNPIVOTが使えないデータベースでも利用可能

欠点

コードが複雑になる
処理速度が遅くなる場合がある

-- 商品カテゴリー別の売上を集計
SELECT
  [商品カテゴリー],
  [月],
  SUM([売上]) AS [売上]
FROM [売上データ]
GROUP BY [商品カテゴリー], [月];

-- 転置処理
WITH pivot_data AS (
  SELECT
    [商品カテゴリー],
    [月],
    [売上]
  FROM #temp
)
SELECT
  [商品カテゴリー],
  [月],
  [売上]
FROM pivot_data
PIVOT
(
  SUM([売上])
  FOR [商品名] IN
  (
    SELECT DISTINCT [商品名]
    FROM [売上データ]
  )
)
ORDER BY [商品カテゴリー], [月];

クロス集計

クロス集計は、SQL Server 2005以降で利用できる機能です。PIVOTと同様の機能を持ちますが、よりシンプルでわかりやすいコードで記述できます。

PIVOTよりもシンプルでわかりやすい
処理速度が速い

SQL Server 2005以降でのみ利用可能

SELECT
  [社員名],
  [月],
  SUM([売上]) AS [売上]
FROM [売上データ]
GROUP BY [社員名], [月]
CROSS APPLY
(
  SELECT
    '1月' AS [月],
    SUM(CASE WHEN [月] = '1月' THEN [売上] ELSE 0 END) AS [売上]
  UNION ALL
  SELECT
    '2月' AS [月],
    SUM(CASE WHEN [月] = '2月' THEN [売上] ELSE 0 END) AS [売上]
  UNION ALL
  ...
  UNION ALL
  SELECT
    '12月' AS [月],
    SUM(CASE WHEN [月] = '12月' THEN [売上] ELSE 0 END) AS [売上]
) AS pivot_data
ORDER BY [社員名], [月];

XML を使った方法

XMLを使って、データを一時的にXML形式に変換し、転置処理を行う方法です。複雑な階層構造を持つデータの転置に有効です。

複雑な階層構造を持つデータの転置に有効

-- 売上データをXML形式に変換
SELECT
  [商品カテゴリー],
  (
    SELECT
      [商品名],
      [売上]
    FOR XML PATH(''), TYPE
  ) AS [商品データ]
FROM [売上データ]
GROUP BY [商品カテゴリー];

-- XMLデータを転置
SELECT
  @xml.value('//商品名[1]', 'NVARCHAR(50)') AS [商品名],
  @xml.value('//売上[1]', 'DECIMAL(10,2)') AS [売上]
FROM @売上データ.xml AS xml
CROSS APPLY
XMLSCHEMA.exist(xml, N'//商品名') AS exist_data;