使用COALESCE+SUM+CASE

问题：将表一变成表二

表一：

stdname stdsubject result

张三     语文    80

张三     数学    90

张三     物理    85

李四     语文    85

李四     数学    92

李四     物理    82

李四     化学    82

表二：

stdname 语文 数学 物理 化学

李四        85    92     82   82

张三        80    90     85   0

问题分析：使用分组解决问题，则写出来的 sql 会是这种格式的：

SELECT stdname,... FROM　#student group by stdname

中间的...如何写呢？

要把表一的记录的值作为表二的字段，则需要使用到 CASE语句，而表一中张三是没有化学成绩的，

所以需要使用 COALESCE。

sql代码：

CREATE TABLE #student (stdname nvarchar(10),stdsubject nvarchar(10),result int)
 INSERT INTO #student VALUES ('张三','语文',80)
 INSERT INTO #student values ('张三','数学',90)
 INSERT INTO #student VALUES ('张三','物理',85)
 INSERT INTO #student VALUES ('李四','语文',85)
 INSERT INTO #student values ('李四','数学',92)
 INSERT INTO #student VALUES ('李四','物理',82)
 INSERT INTO #student VALUES ('李四','化学',82)
           

-- 使用 COALESCE + SUM + CASE
 SELECT stdname,
 COALESCE(SUM(CASE stdsubject WHEN '化学' THEN Result END),0) 化学,
 COALESCE(SUM(CASE stdsubject WHEN '数学' THEN Result END),0) 数学,
 COALESCE(SUM(CASE stdsubject WHEN '物理' THEN Result END),0) 物理,
 COALESCE(SUM(CASE stdsubject WHEN '语文' THEN Result END),0) 语文 
 FROM #student 
 GROUP BY stdname  
 
--使用 拼接 sql 的方法 更简便
--使用 SELECT 为变量赋值，会重复从表里取出所有记录，这是SET不能完成的
DECLARE @sql VARCHAR(1000) 
SET @sql = 'SELECT stdname'
SELECT @sql = @sql + ',COALESCE(SUM(CASE stdsubject WHEN'''+stdsubject+'''THEN Result END),0)['+stdsubject+']'
  FROM (SELECT DISTINCT stdsubject FROM #student) temp
SET @sql = @sql + 'FROM #student GROUP BY stdname'
EXEC (@sql)
           

*实际应用中不会这样设计表，这里这是提供一个执行方法。