CODEWITHSUNDEEP
mysqlpivot
Wen Qing
Wen Qing

Reputation: 111

Pivot Table returning Null value in output

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):

enter image description here

Sample of sourcefile:

enter image description here

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?

Upvotes: 0

Views: 167

Answers (1)

FanoFN
FanoFN

Reputation: 7124

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

Upvotes: 1

Related Questions