集計関数と CASE ステートメントを使用して MySQL でデータを動的にピボットする方法

MySQL の集計関数と CASE ステートメントを使用してデータを動的にピボットする

次のスキーマを持つ製品パーツのテーブルを考えてみましょう:

CREATE TABLE Parts (
    part_id INT NOT NULL,
    part_type CHAR(1) NOT NULL,
    product_id INT NOT NULL,
    PRIMARY KEY (part_id)
);

サンプルデータ:

INSERT INTO Parts (part_id, part_type, product_id) VALUES
(1, 'A', 1),
(2, 'B', 1),
(3, 'A', 2),
(4, 'B', 2),
(5, 'A', 3),
(6, 'B', 3);

目標は、このデータをピボットして製品パーツを列として表すテーブルにすることです:

CREATE TABLE PivotedParts (
    product_id INT NOT NULL,
    part_A_id INT,
    part_B_id INT,
    PRIMARY KEY (product_id)
);

動的ピボット ソリューション:

MySQL には組み込みの PIVOT 関数がありませんが、動的ソリューションは次のとおりです。集計関数と CASE ステートメントを使用して実現します:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when part_type = ''',
      part_type,
      ''' then part_id end) AS part_',
      part_type, '_id'
    )
  ) INTO @sql
FROM
  Parts;

SET @sql = CONCAT('SELECT product_id, ', @sql, ' 
                  FROM Parts 
                   GROUP BY product_id');

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

出力例:

+------------+----------+----------+
| product_id | part_A_id | part_B_id |
+------------+----------+----------+
| 1          | 1        | 2        |
| 2          | 3        | 4        |
| 3          | 5        | 6        |
+------------+----------+----------+

静的ピボット ソリューション (限られた数の列の場合) :

列の数が制限された静的ピボット ソリューションの場合、次のようになります。使用できるクエリ:

SELECT product_id,
  max(case when part_type ='A' then part_id end) as part_A_id,
  max(case when part_type ='B' then part_id end) as part_B_id
FROM Parts
GROUP BY product_id;

出力:

+------------+----------+----------+
| product_id | part_A_id | part_B_id |
+------------+----------+----------+
| 1          | 1        | 2        |
| 2          | 3        | 4        |
| 3          | 5        | 6        |
+------------+----------+----------+

以上が集計関数と CASE ステートメントを使用して MySQL でデータを動的にピボットする方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。