我已经经历了很多类似的问题(MySQL中的行到列转换)和相同的解决方案,但这些解决方案对我不起作用。
预期结果:
Name |S1 |S2 |S3 |S4 |S5 |S6
----------------------------------------
ABC |Pass |Fail |Fail |Pass |Pass |Fail
SQL Query:
----------
SELECT Name,
(CASE WHEN (Semester = 'S1' AND Result= 'Pass') THEN 'Pass' ELSE 'Fail' END) AS S1,
(CASE WHEN (Semester = 'S2' AND Result= 'Pass') THEN 'Pass' ELSE 'Fail' END) AS S2,
(CASE WHEN (Semester = 'S3' AND Result= 'Pass') THEN 'Pass' ELSE 'Fail' END) AS S3,
(CASE WHEN (Semester = 'S4' AND Result= 'Pass') THEN 'Pass' ELSE 'Fail' END) AS S4,
(CASE WHEN (Semester = 'S5' AND Result= 'Pass') THEN 'Pass' ELSE 'Fail' END) AS S5,
(CASE WHEN (Semester = 'S6' AND Result= 'Pass') THEN 'Pass' ELSE 'Fail' END) AS S6
FROM pivot_example
-- GROUP BY Semester
SQL输出:
Name |S1 |S2 |S3 |S4 |S5 |S6
--------------------------------------------------
ABC |Pass |Fail |Fail |Fail |Fail |Fail
ABC |Fail |Fail |Fail |Fail |Fail |Fail
ABC |Fail |Fail |Fail |Fail |Fail |Fail
ABC |Fail |Fail |Fail |Pass |Fail |Fail
ABC |Fail |Fail |Fail |Fail |Pass |Fail
ABC |Fail |Fail |Fail |Fail |Fail |Fail
