最簡捷直觀的方法就是利用分組

select ID,

Sum(語文) 語文,

Sum(數學) 數學,

Sum(英語) 英語

from scores

group by ID

你可以直接用下面的語句在SQL Server中測試結果：

DECLARE @ScoresVar table(

ID int NOT NULL,

Chinese int,

Math int,

English int);

Insert into @ScoresVar

values(1 ,70, null, null)

Insert into @ScoresVar

values(1 ,null,80, null)

Insert into @ScoresVar

values(1 ,null, null, 90)

Insert into @ScoresVar

values(2 ,75, null, null)

Insert into @ScoresVar

values(2 ,null,85, null)

Insert into @ScoresVar

values(2 ,null, null, 95)

select ID,

SUM(Chinese) Chinese,

Sum(Math) Math,

Sum(English) English

from @ScoresVar

group by ID

還有一種方法，可能更通用一點，利用 FOR XML PATH 和STUFF函式來做，雖然複雜，但是對錶的資料型別無限制。

還是上面的測試資料，你可以透過下面的程式碼來得到你想要的資料：

SELECT ID,

STUFF((SELECT "," + Cast(A.Chinese as varchar)

FROM @ScoresVar A Where A.ID=B.ID FOR XML PATH("")),1,1,"") As Chinese,

STUFF((SELECT "," + Cast(A.Math as varchar)

FROM @ScoresVar A Where A.ID=B.ID FOR XML PATH("")),1,1,"") As Math,

STUFF((SELECT "," + Cast(A.English as varchar)

FROM @ScoresVar A Where A.ID=B.ID FOR XML PATH("")),1,1,"") As English

From @ScoresVar B

Group By ID

-- 輸出結果

ID Chinese Math English

1 70 80 90

2 75 85 95