MySQL动态数据透视表列实现：多表关联动态列转换需求

Let's fix the issues in your existing code and build a working dynamic pivot query that adapts to changes in demographic_categories. Here's what was off in your initial attempt:

• Table Name Typo: You referenced test_requirement_demographic but your actual table is test_demographic_requirements.
• Join Logic: Missing a project_id match between tables could lead to unintended results, and using LEFT JOIN might include unnecessary null records if all tests have requirements.

Here's the corrected, fully functional dynamic pivot query:

SET @sql = NULL;

-- Generate dynamic column definitions from demographic categories
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'MAX(CASE WHEN dc.demographic_name = ''',
        demographic_name,
        ''' THEN trd.demographic_value END) AS `',
        demographic_name,
        '`'
    )
) INTO @sql
FROM demographic_categories;

-- Build the complete SQL query
SET @sql = CONCAT(
    'SELECT pt.test_id, pt.group_id, ',
    @sql,
    ' FROM project_tests pt
    JOIN test_demographic_requirements trd 
        ON pt.test_id = trd.test_id AND pt.project_id = trd.project_id
    JOIN demographic_categories dc 
        ON trd.demographic_id = dc.demographic_id
    GROUP BY pt.test_id, pt.group_id
    ORDER BY pt.test_id;'
);

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

How This Works: #

1. Dynamic Column Generation: The first SELECT uses GROUP_CONCAT to build a string of MAX(CASE...) expressions. Each expression maps a demographic category name to its corresponding value for each test. We wrap column names in backticks to handle spaces or special characters in category names.
2. Full Query Construction: We stitch the dynamic columns into the main query, joining all three tables to link test records with their demographic requirements and category labels.
3. Adaptive Execution: Using prepared statements lets us run the dynamically built SQL, which automatically updates if new demographic categories are added to demographic_categories.

Quick Notes: #

• If you have a large number of demographic categories, you may need to increase the group_concat_max_len setting to avoid truncating the dynamic SQL string. Do this temporarily with:

  SET SESSION group_concat_max_len = 1000000;
  

• Switch to LEFT JOIN instead of JOIN if you need to include tests that have no demographic requirements (they'll show NULL values for the category columns).

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