DB2实现行转列

1. 数据表如下：

2. sql实现

2.1 如果我们将科目里面的数学、语文、英语通过case函数选择出来

SELECT 
(CASE `subject` WHEN '数学' THEN score ELSE '0' END) AS "数学",
(CASE `subject` WHEN '语文' THEN score ELSE '0' END) AS "语文",
(CASE `subject` WHEN '英语' THEN score ELSE '0' END) AS "英语"
FROM student

2.2 我们也可以将 id 相同的进行分组，既然分组，我们就需要多新的列进行合并。我这里用的是求和，也可以使用其他聚合函数。

SELECT id,
SUM(CASE `subject` WHEN '数学' THEN score ELSE '0' END) AS "数学",
SUM(CASE `subject` WHEN '语文' THEN score ELSE '0' END) AS "语文",
SUM(CASE `subject` WHEN '英语' THEN score ELSE '0' END) AS "英语"
FROM student GROUP BY id;

2.3 student这个表也可优化。因为真正生产项目中，表不可能只有自己个字段，我们只需要查找自己想要的字段，那你要的条件转化即可。

SELECT id,
SUM(CASE `subject` WHEN '数学' THEN score ELSE '0' END) AS "数学",
SUM(CASE `subject` WHEN '语文' THEN score ELSE '0' END) AS "语文",
SUM(CASE `subject` WHEN '英语' THEN score ELSE '0' END) AS "英语"
FROM (SELECT id,SUBJECT,score FROM student) AS a GROUP BY id;

表a,就是自己重新构建的新表，表可以多表联查。