SQLのピボット入門：ピボットテーブルの作成方法

はじめに

データベース操作において、データの行を列に変換することはよくあります。これをピボット操作と呼びます。ピボット操作を使用することで、データの集計や分析がより直感的に行えるようになります。今回は、SQLでピボットテーブルを作成する方法について解説します。

ピボット操作の概要

ピボット操作は、データの特定の列の値を列ヘッダーとして使用し、他の列の値をこれらの新しい列に配置することです。これにより、クロス集計やデータの要約が容易になります。

以下のような売上データを持つテーブルsalesがあるとします。

このデータをピボット操作で次のように変換したいとします。

MySQLでのピボット操作

MySQLでは、ピボット操作を実現するために条件付き集計関数を使用します。

SELECT 
  salesperson,
  SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS Jan,
  SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb
FROM sales
GROUP BY salesperson;

このクエリは、salesテーブルのデータをピボットし、各営業担当者の月別売上を列に変換して表示します。

MySQLでの動的ピボット

動的ピボットは、ピボットする列の数が固定されていない場合に有用です。例えば、月の数が固定されていない場合などです。

MySQLでの動的ピボットは、準備されたステートメントを使用して実行できます。

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN month = ''',
      month,
      ''' THEN sales ELSE 0 END) AS ',
      month
    )
  ) INTO @sql
FROM sales;

SET @sql = CONCAT('SELECT salesperson, ', @sql, ' FROM sales GROUP BY salesperson');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

このクエリは、salesテーブルのデータを動的にピボットし、各営業担当者の月別売上を列に変換して表示します。

ピボットのポイント

ピボット操作を行う際のポイントをまとめます。

CASE WHEN + SUMの組み合わせがピボットの基本パターンです。SUMの代わりにCOUNT、MAX、AVGなど他の集合関数も使用できます。

列の値が事前に分かっている場合は静的ピボットが簡単です。列の値が動的に変わる場合は、準備されたステートメント（PREPARE/EXECUTE）を使用します。

Excelのピボットテーブルと同じ結果をSQLで実現できるため、大量データの集計に便利です。