MAX() および CASE ステートメントを使用して MySQL 列データを行にピボットする方法

MySQL ピボット テーブルの列データを行として表示する

テーブルの列データを行に変換することは、特に多次元の場合、困難な作業になる可能性があります。データセット。 MySQL では、この操作は一般に「ピボット」として知られています。

問題:

提供されたデータセットは 3 つのテーブルで構成されています:

• 質問: 質問を保存しますタイトル。
• 結果: 特定のジョブ ID に対するユーザーの応答を記録します。
• 回答: 各質問と結果の組み合わせに対する回答が含まれます。

目標は、データを再構成して、質問の回答を各結果の列として表示することです。 set.

解決策:

これを実現するには、MAX() 集計関数を CASE ステートメントと組み合わせて活用する MySQL クエリを利用できます。以下のクエリは、列データを効果的に行にピボットします:

SELECT  a.ID,
        a.user_ID,
        a.job_id,
        MAX(CASE WHEN c.question = 'Is it this?' THEN b.answer END) 'Is it this?',
        MAX(CASE WHEN c.question = 'Or this?' THEN b.answer END) 'Or this?',
        MAX(CASE WHEN c.question = 'Or that? ' THEN b.answer END) 'Or that? '
FROM    Results a
        INNER JOIN Answers b
            ON a.id = b.fk_result_id
        INNER JOIN Question c
            ON b.fk_question_id = c.ID
GROUP   BY a.ID,
        a.user_ID,
        a.job_id

説明:

• クエリは、Results (a) テーブルからさまざまなフィールドを選択します。これには、結果 ID、ユーザー ID、ジョブ ID が含まれます。
• 次に、INNER JOIN を使用して、対応するデータを取得します。回答 (b) テーブルからの回答。
• 別の INNER JOIN が質問 (c) テーブルにリンクし、各回答の実際の質問を識別します。
• MAX() 関数が CASE 内で適用されます。ステートメントを使用して、結果と質問の組み合わせごとに回答を集計します。
• 最後に、結果は結果 ID、ユーザー ID、ジョブ ID でグループ化され、目的の行が作成されます。

このクエリは次の出力を生成します:

result_id	user_id	job_id	Is it this?	Or this?	Or that?
1	1	1	Yes	No	Maybe
2	1	3	Maybe	No	Maybe
3	2	3	Yes	Yes	No

動的バージョン:

質問の数が不明な場合(例: 1000)、動的バージョンのクエリの方が適しています。これは、動的 SQL と変数連結を使用して実現できます。

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN c.question = ''',
      question,
      ''' then b.answer end) AS ',
      CONCAT('`',question,'`')
    )
  ) INTO @sql
FROM Question;

SET @sql = CONCAT('SELECT  a.ID,
                            a.user_ID,
                            a.job_id, ', @sql, ' 
                    FROM    Results a
                            INNER JOIN Answers b
                                ON a.id = b.fk_result_id
                            INNER JOIN Question c
                                ON b.fk_question_id = c.ID
                    GROUP   BY a.ID,
                            a.user_ID,
                            a.job_id');

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

この動的クエリは、変数連結を使用して、Question テーブルの内容に基づいて SQL ステートメントを動的に構築します。その結果、任意の数の質問を処理できる、より柔軟なクエリが作成されます。

以上がMAX() および CASE ステートメントを使用して MySQL 列データを行にピボットする方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。