レポート作成のために MySQL で列を行にピボットする方法

MySQL Pivot: 列を行に変換

MySQL などのリレーショナル データベースでは、レポート目的でデータを変換する必要があることがよくあります。このような変換の 1 つは、列を行に変換する「ピボット」と呼ばれるプロセスです。この手法により、より柔軟で簡潔なデータ表現が可能になります。

質問:

次の MySQL テーブルについて考えてみましょう:

CREATE TABLE mytable (
  id INT, 
  month VARCHAR(3), 
  col1 VARCHAR(1), 
  col2 VARCHAR(1), 
  col3 VARCHAR(1), 
  col4 VARCHAR(1)
);

INSERT INTO mytable (id, month, col1, col2, col3, col4) VALUES
(101, 'Jan', 'A', 'B', NULL, 'B'),
(102, 'Feb', 'C', 'A', 'G', 'E');

目標は、col1 からcol4 までの値を行として表示し、1 月と 2 月の月を列として表示するレポートを作成することです。

解決策:

MySQL には、データのアンピボットまたはピボットのための組み込み関数が提供されていません。ただし、UNION ALL と CASE 式を使用した集計を使用して、これらの操作をシミュレートできます。

1. 反視点:

データのピボットを解除するには、UNION ALL を使用してすべての列の行を 1 つの列に結合する新しいサブクエリを作成します。

SELECT id, month, col1 AS `value`, 'col1' AS `descrip`
UNION ALL
SELECT id, month, col2 AS `value`, 'col2' AS `descrip`
UNION ALL
SELECT id, month, col3 AS `value`, 'col3' AS `descrip`
UNION ALL
SELECT id, month, col4 AS `value`, 'col4' AS `descrip`
FROM mytable;

2. 視点:

次に、アンピボット クエリをサブクエリでラップし、集計と CASE ステートメントを使用してデータを必要な形式に変換します。

SELECT descrip, 
  MAX(CASE WHEN month = 'Jan' THEN `value` ELSE NULL END) AS Jan,
  MAX(CASE WHEN month = 'Feb' THEN `value` ELSE NULL END) AS Feb
FROM (
  SELECT id, month, `value`, descrip
  FROM (
    SELECT id, month, col1 AS `value`, 'col1' AS `descrip`
    UNION ALL
    SELECT id, month, col2 AS `value`, 'col2' AS `descrip`
    UNION ALL
    SELECT id, month, col3 AS `value`, 'col3' AS `descrip`
    UNION ALL
    SELECT id, month, col4 AS `value`, 'col4' AS `descrip`
    FROM mytable
  ) AS unpivoted
) AS src
GROUP BY descrip;

結果:

Descrip	Jan	Feb
col1	A	C
col2	B	A
col3	NULL	G
col4	B	E

注: ELSE 0 END を ELSE NULL END に変更して、結果をデータベース仕様とより一致させ、不必要な 0 値を避けます。

以上がレポート作成のために MySQL で列を行にピボットする方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。