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

Question

这个问题已经有答案了:

MySQL pivot row into dynamic number of columns (1 个回答)

已关闭 3 年前。

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

Answer 1

a) 为了生成一行，请更改:

GROUP BY Semester

进入:

GROUP BY Name

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