MySQL の行を複数の列に基づいた列に変換するにはどうすればよいですか?

複数の列に基づいて行を列に変換する MySQL クエリ

概要:

動的に行から列への変換は、データ操作における一般的なタスクです。この記事では、単一列の変換を処理する以前のソリューションの機能を拡張する、MySQL クエリを使用したソリューションを検討します。

問題:

データと対応する価格を表す複数の行が注文と品目ごとに整理されたテーブル。目標は、このデータを、各注文が列として表され、各商品が行として表され、対応する価格が表示される表に変換することです。

解決策:

この複数列の変換を実現するには、2 段階のプロセスを採用します。

ステップ 1: ピボットを解除するデータ

まず、UNION ALL を使用してデータをアンピボットし、注文と商品の組み合わせごとに複数の行を作成します。これにより、次のステップでデータをより簡単に操作できるようになります。

SELECT id, CONCAT('order', `order`) col, data value
FROM tableA
UNION ALL
SELECT id, CONCAT('item', item) col, price value
FROM tableA;

ステップ 2: アンピボットされたデータのピボット

データがアンピボットされたら、ピボットしますCASE ステートメントで集計関数を使用して列に戻します。これにより、ピボットされていない行が ID でグループ化され、対応する値が適切な列に割り当てられます。

SELECT
  id,
  MAX(CASE WHEN col = 'order1' THEN value END) ORDER1,
  MAX(CASE WHEN col = 'order2' THEN value END) ORDER2,
  MAX(CASE WHEN col = 'order3' THEN value END) ORDER3,
  MAX(CASE WHEN col = 'item1' THEN value END) ITEM1,
  MAX(CASE WHEN col = 'item2' THEN value END) ITEM2,
  MAX(CASE WHEN col = 'item3' THEN value END) ITEM3
FROM
(
  SELECT id, CONCAT('order', `order`) col, data value
  FROM tableA
  UNION ALL
  SELECT id, CONCAT('item', item) col, price value
  FROM tableA
) d
GROUP BY id;

動的クエリ生成:

注文数が異なるテーブルを処理するにはとアイテムを使用すると、準備されたクエリを使用して動的にクエリを生成できます。ステートメント。

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN col = ''',
      col,
      ''' THEN value END) AS `',
      col, '`')
  ) INTO @sql
FROM
(
  SELECT CONCAT('order', `order`) col
  FROM tableA
  UNION ALL
  SELECT CONCAT('item', `item`) col
  FROM tableA
)d;

SET @sql = CONCAT('SELECT id, ', @sql, '
                  FROM
                  (
                    SELECT id, CONCAT(''order'', `order`) col,  data value
                    FROM tableA
                    UNION ALL
                    SELECT id, CONCAT(''item'', item) col, price value
                    FROM tableA
                  ) d
                  GROUP BY id');

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

結果:

結果のテーブルは、各注文を列として、各アイテムを行として、データを簡潔に表現します。対応する価格。

以上がMySQL の行を複数の列に基づいた列に変換するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。