Pivot Table returning Null value in output-CodePudding

I am trying to get a single row for each 'resp_id', with another 2 columns beside it. However, only one output is shown while the other keeps showing a null value.

SELECT `resp_id`,
    CASE 
        WHEN survey.`var`='quality' 
        THEN survey.`val`
        ELSE NULL    
    END
AS `quality`,
    CASE 
        WHEN survey.`var`='receive_menu' 
        THEN survey.`val`
        ELSE NULL 
    END
AS `receive_menu`
FROM survey
GROUP BY survey.`resp_id`;

Output (Sample):

Sample of sourcefile:

I have also tried to play around but only the value inside the 'quality' column is shown but not the rest. Are there any reasons it does not work?

CodePudding user response：

You're almost there, you just need to add GROUP_CONCAT() or SUM() to make it work:

SELECT `resp_id`,
    GROUP_CONCAT(CASE 
        WHEN survey.`var`='quality' 
        THEN survey.`val`
    END)
AS `quality`,
    GROUP_CONCAT(CASE 
        WHEN survey.`var`='receive_menu' 
        THEN survey.`val`
    END)
AS `receive_menu`
FROM survey
GROUP BY survey.`resp_id`;

I removed ELSE NULL from the CASE expression because it's already returning NULL by default. Use ELSE only when you want to return something else than NULL.

Demo fiddle