使用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)
           

*實際應用中不會這樣設計表，這裡這是提供一個執行方法。