when i try get the feedback items, i receipt only the first column, why?

SELECT
u.firstname as "Usuario",

 CASE WHEN i.name = 'task 1' THEN v.value END AS "1",
 CASE WHEN i.name = 'task 2' THEN v.value END AS "2",
 CASE WHEN i.name = 'task 3' THEN v.value END AS "3",
 CASE WHEN i.name = 'task 4' THEN v.value END AS "4",
 CASE WHEN i.name = 'task 5' THEN v.value END AS "5",
 CASE WHEN i.name = 'task 6' THEN v.value END AS "6",
 CASE WHEN i.name = 'task 7' THEN v.value END AS "7",
 CASE WHEN i.name = 'task 8' THEN v.value END AS "8",
 CASE WHEN i.name = 'task 9' THEN v.value END AS "9",
 CASE WHEN i.name = 'task 10' THEN v.value END AS "10",
 CASE WHEN i.name = 'task 11' THEN v.value END AS "11",
 CASE WHEN i.name = 'task 12' THEN v.value END AS "12"

FROM prefix_feedback AS f
JOIN prefix_course AS c ON c.id=f.course
JOIN prefix_feedback_item AS i ON f.id=i.feedback
JOIN prefix_feedback_completed AS fc ON f.id=fc.feedback
LEFT JOIN prefix_feedback_value AS v ON v.completed=fc.id AND v.item=i.id
JOIN prefix_user AS u ON fc.userid=u.id

GROUP BY u.firstname

ORDER BY u.id, i.id, i.name

and when i remove the group by i get all columns but on a lot of rows

capture

CodePudding user response：

The firstname column isn't unique, group by the user id or username

GROUP BY u.id

or

GROUP BY u.username

Also the CASE statement needs an ELSE otherwise there is no value.

CASE WHEN i.name = 'task 1' THEN v.value ELSE 0 END AS task1,

Also the GROUP BY statement is used for sums and counts etc. So you are probably missing SUM or MAX ?

SUM(CASE WHEN i.name = 'task 1' THEN v.value ELSE 0 END) AS task1,

or

MAX(CASE WHEN i.name = 'task 1' THEN v.value ELSE 0 END) AS task1,