MySQL 8.0

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

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