SQL 錶轉置關鍵字 case when ，pivot

在 SQL Server 2005 之前，通常需要透過 case 子句來處理。

SQL 2005 後面可以使用pivot

下面介紹case when 用法，SQL 如下：

select ProductID,

sum( case when OrderMonth = 5 then SubTotal end ) as 五月,

sum( case when OrderMonth = 6 then SubTotal end ) as 六月,

sum( case when OrderMonth = 7 then SubTotal end ) as 七月

from Orders

group by ProductID

pivot 用法：SQL 如下：

select sid, [1] as [database], [2] as [CSharp], [3] as [Xml]

from ( select sid, cid, mark from tbl_marks) t pivot

(

sum( mark )

for cid in ( [1], [2], [3] )

) as pvt