dynamic_pivot_test, MySQL

Language: Layout:

CREATE TABLE demographic_categories(
	demographic_id INT,
    demographic_name varchar(10)
);

CREATE TABLE project_tests(
	test_id int,
    group_id int,
    project_id int
);

CREATE TABLE test_demographic_requirements(
	test_id INT,
    project_id INT,
    demographic_id INT,
    demographic_value VARCHAR(10)
);

INSERT INTO demographic_categories(demographic_id, demographic_name)
SELECT 1, 'Color'
UNION ALL
SELECT 2, 'Age Group';

INSERT INTO project_tests(test_id, group_id, project_id)
VALUES (1,1,1),(2,1,1);

INSERT INTO test_demographic_requirements(test_id, project_id, demographic_id, demographic_value)
VALUES (1,1,1,'Blue'),(1,1,2,'Young'),(2,1,1,'Green'),(2,1,2,'Middle');

SET @sql = NULL;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'max(case when dc.demographic_name = ''',
          demographic_name,
          ''' then trd.demographic_value end) AS ',
          replace(demographic_name, ' ', '')
        )
      ) INTO @sql
    from demographic_categories;

SET @sql = CONCAT('SELECT pt.test_id, pt.group_id,
', @sql,'
from test_demographic_requirements trd
LEFT JOIN demographic_categories dc ON trd.demographic_id = dc.demographic_id
LEFT JOIN project_tests pt ON pt.test_id = trd.test_id and pt.project_id =1
group by pt.test_id;');

PREPARE stmt FROM @sql;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;


absolute service time: 0,4 sec