MySQL多条件列转行查询：实现问卷用户答案按列展示

Got it, let's break down how to turn your row-based survey data into a columnar view where each row represents a user, with dedicated columns for every question showing their selected answer.

Static Solution (Fixed Number of Questions) #

If you know all your question IDs and content upfront, conditional aggregation is the straightforward way to pivot the data. Here's an example assuming you have three questions with IDs 1, 2, 3:

SELECT
    s.survey_id AS `User ID`,
    -- Map each question to a column, pulling the corresponding answer content
    MAX(CASE WHEN q.id = 1 THEN a.content END) AS `What's your favorite color?`,
    MAX(CASE WHEN q.id = 2 THEN a.content END) AS `How old are you?`,
    MAX(CASE WHEN q.id = 3 THEN a.content END) AS `Do you prefer cats or dogs?`
FROM survey s
-- Link survey responses to their answer details
JOIN answers a ON s.answer = a.id
-- Connect answers to their parent questions
JOIN questions q ON a.question = q.id
-- Group by user to collapse all their answers into one row
GROUP BY s.survey_id;

How this works:

• The JOINs tie together survey responses, their associated answers, and the parent questions.
• CASE WHEN filters rows for each specific question and pulls the matching answer content.
• MAX() (or MIN() works equally well here) ensures we only keep the single valid answer per user per question, ignoring NULL values from other questions.
• If a user skipped a question, that column will show NULL—you can add ELSE 'No Response' inside the CASE clause to replace this with a more readable default.

Dynamic Solution (Variable Number of Questions) #

If your question set changes over time and you don't want to update the query manually, use dynamic SQL to auto-generate pivot columns from your questions table:

-- Generate the dynamic column list based on existing questions
SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(CASE WHEN q.id = ', q.id, ' THEN a.content END) AS `', q.content, '`'
        )
    ) INTO @sql
FROM questions q;

-- Assemble the full pivot query
SET @sql = CONCAT(
    'SELECT s.survey_id AS `User ID`, ', 
    @sql, 
    ' FROM survey s JOIN answers a ON s.answer = a.id JOIN questions q ON a.question = q.id GROUP BY s.survey_id'
);

-- Execute the dynamically built query
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Notes on the dynamic approach:

• GROUP_CONCAT automatically creates all the CASE statements needed for every question in your database.
• If you have a large number of questions, adjust group_concat_max_len first (e.g., SET SESSION group_concat_max_len = 10000;) to avoid truncating the generated SQL.
• This query will automatically include any new questions added to the questions table without manual edits.

内容的提问来源于stack exchange，提问作者Matt