行の値から動的列名を使用して MySQL テーブルをピボットする方法

MySQL: 列名としての動的行値

質問:

可変カラム名を持つ古いテーブルを、特定のカラムの一意の各行値がカラム名になる固定カラム名を持つテーブルに変換する MySQL クエリを構築するにはどうすればよいですか?

背景:

次の構造を持つテーブルについて考えてみましょう:

<code>id    name       value
------------------------------
0     timezone   Europe/London
0     language   en
0     country    45
0     something  x
1     timezone   Europe/Paris
1     language   fr
1     country    46</code>

要件:

テーブルを次の形式に変換します:

<code>id    timezone       language    country  something
---------------------------------------------------
0     Europe/London  en          45       x
1     Europe/Paris   fr          46</code>

MySQL はネイティブのピボット操作をサポートしていないため、回避策を見つける必要があります。

解決策:

• GROUP_CONCAT() 関数を使用すると、目的のテーブル形式を作成するクエリを動的に生成できます。
• 最初のクエリは、GROUP_CONCAT() を使用して最終クエリを構築する文字列を準備します。
• 2 番目のクエリは、PREPARE と EXECUTE を使用して動的に生成されたクエリを実行します。

クエリ:

<code class="language-sql">SELECT CONCAT(
  'SELECT `table`.id', GROUP_CONCAT('
     ,    `t_', REPLACE(name, '`', '``'), '`.value
         AS `', REPLACE(name, '`', '``'), '`'
     SEPARATOR ''),
 ' FROM `table` ', GROUP_CONCAT('
     LEFT JOIN `table`   AS `t_', REPLACE(name, '`', '``'), '`
            ON `table`.id = `t_', REPLACE(name, '`', '``'), '`.id
           AND `t_', REPLACE(name, '`', '``'), '`.name = ', QUOTE(name)
     SEPARATOR ''),
 ' GROUP BY `table`.id'
) INTO @qry FROM (SELECT DISTINCT name FROM `table`) t;

PREPARE stmt FROM @qry;
EXECUTE stmt;</code>

注:

• GROUP_CONCAT() 結果の長さは group_concat_max_len 変数によって制限されます (デフォルトは 1024 バイト)。
• または、CASE WHEN または複数の副選択/結合を含む準備済みステートメントを使用することもできますが、これには一意の列値を手動で処理する必要があります。

以上が行の値から動的列名を使用して MySQL テーブルをピボットする方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。