By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE source_table (
id INT,
respondent_id INT,
demographic VARCHAR(255),
question VARCHAR(255),
answer VARCHAR(255));
INSERT INTO source_table VALUES
('1','1',NULL,'Age','30'),
('2','1',NULL,'education','masters'),
('3','1',NULL,'height','1.8m'),
('4','1',NULL,'income','$1'),
('5','1',NULL,'address','ISS'),
('6','1',NULL,'talent','dancing'),
('7','2',NULL,'Age','20'),
('8','2',NULL,'education','highschool'),
('9','2',NULL,'height','4m'),
('10','2',NULL,'income','$3.2'),
('11','2',NULL,'address','Highsea'),
('12','2',NULL,'talent','singing');
show warnings;
SET @sql = NULL;
SELECT
Concat(',', GROUP_CONCAT(DISTINCT
CONCAT(
'(SELECT l.answer
FROM source_table l where l.respondent_id = a.respondent_id
AND l.question = "', b.question,'") AS ',b.question),
'')
) INTO @sql
FROM source_table b
WHERE b.demographic IS NOT NULL;
SET @sql := IF(@sql IS NULL,'',@sql);
SET @sql =
CONCAT('SELECT respondent_id ',@sql,' , a.question , a.answer
FROM source_table a
WHERE a.demographic IS NULL
GROUP BY respondent_id, a.question , a.answer
');
select @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
| @sql |
|---|
| SELECT respondent_id , a.question , a.answer FROM source_table a WHERE a.demographic IS NULL GROUP BY respondent_id, a.question , a.answer |
| respondent_id | question | answer |
|---|---|---|
| 1 | Age | 30 |
| 1 | education | masters |
| 1 | height | 1.8m |
| 1 | income | $1 |
| 1 | address | ISS |
| 1 | talent | dancing |
| 2 | Age | 20 |
| 2 | education | highschool |
| 2 | height | 4m |
| 2 | income | $3.2 |
| 2 | address | Highsea |
| 2 | talent | singing |