Test, MySQL - rextester

Language: Layout:

#mysql 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating

DROP TABLE IF EXISTS `Questions`;
DROP TABLE IF EXISTS `Answers`;
DROP TABLE IF EXISTS `yourTable`;

CREATE TABLE `Questions`
    (`id` int, `Question` varchar(17))
;
    
INSERT INTO `Questions`
    (`id`, `Question`)
VALUES
    (1, 'question1'),
    (2, 'question2'),
    (3, 'question3'),
    (4, 'question4')
;

CREATE TABLE `Answers`
    (`id` int, `choice` varchar(17))
;
    
INSERT INTO `Answers`
    (`id`, `choice`)
VALUES
    (1, 'Strongly Agree'),
    (2, 'Agree'),
    (3, 'Disagree'),
    (4, 'Strongly Disagree')
;

CREATE TABLE yourTable
    (`question1` varchar(14), `question2` varchar(17), `question3` varchar(5), `question4` varchar(8))
;
    
INSERT INTO yourTable
    (`question1`, `question2`, `question3`, `question4`)
VALUES
    ('Agree', 'Disagree', 'Agree', 'Disagree'),
    ('Strongly Agree', 'Strongly Disagree', 'Agree', 'Disagree')
;

SELECT Question,
       COUNT(CASE WHEN data = 'Strongly Agree' THEN 1 END)    as `Strongly Agree`,
       COUNT(CASE WHEN data = 'Agree' THEN 1 END)             as `Agree`,
       COUNT(CASE WHEN data = 'Disagree' THEN 1 END)          as `Disagree`,
       COUNT(CASE WHEN data = 'Strongly Disagree' THEN 1 END) as `Strongly Disagree`
FROM (       
        SELECT Q.Question, A.choice, p.`data`
        FROM `Questions` Q
        CROSS JOIN `Answers` A
        LEFT JOIN (
                    select c.col,
                           case c.col
                             when 'question1' then question1
                             when 'question2' then question2
                             when 'question3' then question3
                             when 'question4' then question4
                           end as `data` 
                    from yourTable t     
                    cross join
                    (
                      select 'question1' as col
                      union all select 'question2'
                      union all select 'question3'
                      union all select 'question4'
                    ) c
                  ) P
              ON A.`choice` = p.`data` 
             AND Q.Question = P.`col`
     ) R           
GROUP BY  Question;


absolute service time: 0,46 sec