MySQL のピボットテーブル

Sweety Rupani 2023年1月30日
  1. MySQL で MAX 関数と IF ステートメントを使用してピボットテーブルを作成する
  2. MySQL で CASE ステートメントを使用してピボットテーブルを作成する
  3. MySQL で動的ピボット列を使用してピボットテーブルを作成する
MySQL のピボットテーブル

この記事では、テーブルデータを行から列に変換する方法について説明します。このプロセスはピボットと呼ばれます。この変換の結果は通常、レポート生成に適した情報を提示する要約テーブルです。

MySQL には、ピボットテーブルを作成するための組み込み関数がないため、ピボットテーブルを生成するために MySQL クエリを作成する必要があります。幸い、MySQL を使用してピボットテーブルを作成するには、3つの異なる方法があります。

  1. IF ステートメントを使用して MySQL でピボットテーブルを作成します
  2. CASE ステートメントを使用して MySQL でピボットテーブルを作成します
  3. 動的ピボット列を使用して MySQL でピボットテーブルを作成します

次のスクリプトは、3つの列(namesubjectid、および marks)を持つテーブル student を作成します。

create table student(name varchar(20),subjectid int(10), marks int(10));

ここでは、デモンストレーションのためにサンプルデータ値を student テーブルに挿入します。

insert into student values ('Sam',1,70);
insert into student values ('Sam',2,77);
insert into student values ('Sam',3,71);
insert into student values ('Sam',4,70);
insert into student values ('Sam',1,99);
insert into student values ('John',1,89);
insert into student values ('John',2,87);
insert into student values ('John',3,88);
insert into student values ('John',4,89); 
insert into student values ('Martin',1,60);
insert into student values ('Martin',2,47);
insert into student values ('Martin',3,68);
insert into student values ('Martin',4,39);

これらの値を挿入した後、データベース内のレコードを表示するための select クエリを記述しましょう。

Select * from student;

出力は次のようになります。

mysql のピボットテーブル-例

MySQL で MAX 関数と IF ステートメントを使用してピボットテーブルを作成する

これは、問題に対する非常に簡単なアプローチです。このアプローチでは、MAX 関数と IF ステートメントを使用します。1つの科目の複数のエントリがデータベース内の学生に存在する場合、最大 2つのマークが必要になります。

SELECT クエリでこれを使用する方法を見てみましょう。

SELECT name,
MAX(IF(subjectid=1, marks, NULL)) AS Sub1,
MAX(IF(subjectid=2, marks, NULL)) AS Sub2,
MAX(IF(subjectid=3, marks, NULL)) AS Sub3,
MAX(IF(subjectid=4, marks, NULL)) AS Sub4
FROM student
GROUP BY name;

出力は次のようになります。

mysql のピボットテーブル-if ステートメント

この出力は、各学生の科目ごとにまとめられた結果です。生徒ごとに 1つの行が必要なので、名前の列でグループ化する必要があります。また、列ごとに 1つの条件、つまり、サブジェクトごとに 1つの条件を指定する必要があります。

MySQL で CASE ステートメントを使用してピボットテーブルを作成する

これは、問題への簡単なアプローチでもあります。このアプローチでは、MAX 関数を CASE ステートメントと一緒に使用します。また、データベース内の学生に 1つの科目の複数のエントリが存在する場合、最大 2つのマークが付けられます。同様に、一部の生徒にマークが存在しない場合は、NULL 値を取ります。

SELECT クエリでこれを使用する方法を見てみましょう。

SELECT name,
MAX(CASE WHEN subjectid=1 THEN marks ELSE NULL END) AS Sub1,
MAX(CASE WHEN subjectid=2 THEN marks ELSE NULL END) AS Sub2,
MAX(CASE WHEN subjectid=3 THEN marks ELSE NULL END) AS Sub3,
MAX(CASE WHEN subjectid=4 THEN marks ELSE NULL END) AS Sub4
FROM student
GROUP BY name; 

出力は次のようになります。

mysql のピボットテーブル-case ステートメント

MySQL で動的ピボット列を使用してピボットテーブルを作成する

集計関数(MAX)、IF ステートメント、および CASE ステートメントは、上記の例のピボットテーブルを生成しました。このアプローチを使用することの欠点は、クエリの記述中に列見出しを知る必要があることです。列の数が増えると、コードも増えます。上記の両方のアプローチで、より小さな結果とすべての可能な値を得ることができます。

したがって、これらの制限を克服するために、動的ピボット列を使用できます。ここで、GROUP_CONCAT 関数は、PIVOT テーブル出力の列を動的に生成できます。

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(
 ' MAX(CASE WHEN subjectid = ', subjectid, ' THEN marks ELSE 0 END) 
 AS "', subjectid, '"')
)
INTO @sql FROM student;

SET @sql = CONCAT('SELECT name, ', @sql, 
 ' FROM student GROUP BY name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

GROUP_CONCAT 関数では、DISTINCT キーワードを使用して、マークの一意のリストを取得します。そうしないと、リストが長すぎるため、クエリでエラーが発生します。Select * from @sql と書くと、いつでも動的クエリをチェックできます。

このクエリを実行すると、結果として次のようになります。

mysql のピボットテーブル-動的ピボット列

列ヘッダーはテーブルの値に基づいて動的に生成され、列ヘッダーは subjectid を表すことに注意してください。

関連記事 - MySQL Table