add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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','checked','Age','30'),
('2','1',NULL,'education','masters'),
('3','1','checked','height','1.8m'),
('4','1',NULL,'income','$1'),
('5','1',NULL,'address','ISS'),
('6','1',NULL,'talent','dancing'),
('7','2','checked','Age','20'),
('8','2',NULL,'education','highschool'),
('9','2','checked','height','4m'),
('10','2',NULL,'income','$3.2'),
('11','2',NULL,'address','Highsea'),
('12','2',NULL,'talent','singing');
Records: 12  Duplicates: 0  Warnings: 0
SET @sql = NULL;


SELECT
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;

select @sql;
SET @sql =
CONCAT('SELECT respondents_id,
',@sql,',
a.question , a.answer
FROM source_table a
WHERE a.demographic IS NULL
GROUP BY id
');
select @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
@sql
SELECT l.answer FROM source_table l where l.respondent_id = a.respondent_id AND l.question = Age
      AS Age,SELECT l.answer FROM source_table l where l.respondent_id = a.respondent_id AND l.question = height
      AS height
@sql
SELECT respondents_id,
              SELECT l.answer FROM source_table l where l.respondent_id = a.respondent_id AND l.question = Age
      AS Age,SELECT l.answer FROM source_table l where l.respondent_id = a.respondent_id AND l.question = height
      AS height,
        a.question , a.answer
        FROM source_table a
        WHERE a.demographic IS NULL
        GROUP BY id
       		 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT l.answer FROM source_table l where l.respondent_id = a.respondent_id AND ' at line 2
show warnings;
Level Code Message
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT l.answer FROM source_table l where l.respondent_id = a.respondent_id AND ' at line 2
SET @sql = NULL;

SELECT
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;

select @sql;

SET @sql =
CONCAT('SELECT respondent_id, a.question , a.answer,
',@sql,'
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 l.answer
          FROM source_table l where l.respondent_id = a.respondent_id
          AND l.question = "Age") AS Age,(SELECT l.answer
          FROM source_table l where l.respondent_id = a.respondent_id
          AND l.question = "height") AS height
@sql
SELECT respondent_id, a.question , a.answer,
               (SELECT l.answer
          FROM source_table l where l.respondent_id = a.respondent_id
          AND l.question = "Age") AS Age,(SELECT l.answer
          FROM source_table l where l.respondent_id = a.respondent_id
          AND l.question = "height") AS height
        FROM source_table a
        WHERE a.demographic IS NULL
        GROUP BY respondent_id, a.question , a.answer
       		 
Statement prepared
respondent_id question answer Age height
1 education masters 30 1.8m
1 income $1 30 1.8m
1 address ISS 30 1.8m
1 talent dancing 30 1.8m
2 education highschool 20 4m
2 income $3.2 20 4m
2 address Highsea 20 4m
2 talent singing 20 4m