mysql - 在 MySQL 中将行转换为列以获取自定义值

Question

我经历了很多类似的问题（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

标签： mysqlpivot-table

Answer 1

a) 为了产生一行，改变：

GROUP BY Semester

进入：

GROUP BY Name

b) 对于通过/失败值，可能需要查询，具体取决于表的外观。