MySQL で動的列を行にアンピボットするにはどうすればよいですか?

動的列からの行の作成

データ管理の領域では、多くの場合、列と行の操作が必要になります。この質問は、複数の動的列を、特定の基準を含む行を含む新しいテーブルに変換するという特定のタスクについて詳しく掘り下げています。

指定された例は、Table_1 のアンピボットを示し、結果として Expected_Result_Table になります。主な違いは、Expected_Result_Table ではゼロ以外の各値が個別の行に表示されることです。

MySQL には直接 UNPIVOT 関数がありませんが、この制限は UNION ALL ステートメントを使用して克服できます。基本的なアプローチには、列ごとに個別の SELECT ステートメントを作成し、ID、「word」値として列名、「qty」値として列値を選択することが含まれます。これらの SELECT ステートメントは、UNION ALL を使用して結合されます。

例:

SELECT id, 'abc' AS word, abc AS qty
FROM yt
WHERE abc > 0
UNION ALL
SELECT id, 'brt', brt
FROM yt
WHERE brt > 0

このアプローチは、動的列を扱う場合にはさらに複雑になります。これに対処するには、準備されたステートメントを使用できます。これらにより、メタデータに基づいて SQL ステートメントを動的に生成できます。

次のコード スニペットは、そのようなアプローチを示しています。

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select id, ''',
      c.column_name,
      ''' as word, ',
      c.column_name,
      ' as qty 
      from yt 
      where ',
      c.column_name,
      ' > 0'
    ) SEPARATOR ' UNION ALL '
  ) INTO @sql
FROM information_schema.columns c
WHERE c.table_name = 'yt'
  AND c.column_name NOT IN ('id')
ORDER BY c.ordinal_position;

SET @sql 
  = CONCAT('select id, word, qty
           from
           (', @sql, ') x  order by id');


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

この例では、動的 SQL は、 information_schema.columns テーブル。次に、プリペアド ステートメント stmt を使用して動的に生成された SQL を実行し、動的列を行に効果的にアンピボットします。

このアプローチを実装することで、特定のデータ ニーズに合わせて動的列を行に変換することができます。

以上がMySQL で動的列を行にアンピボットするにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。