SQL Server：使用 PIVOT 行转列和 UNPIVOT 列转行

SELECT <非透视的列>,
    [第一个透视的列] AS <列名称>,
    [第二个透视的列] AS <列名称>,
    ...
    [最后一个透视的列] AS <列名称>,
FROM
    (<生成数据的 SELECT 查询>)
    AS <源查询的别名>
PIVOT
(
    <聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
    IN ( [第一个透视的列], [第二个透视的列],
    ... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;

--A1、传统 case 方法
select username as '姓名'
,max(case [subject] when '语文' then score else 0 end) as '语文'
,max(case [subject] when '数学' then score else 0 end) as '数学'
,max(case [subject] when '英语' then score else 0 end) as '英语'
,max(case [subject] when '生物' then score else 0 end) as '生物'
from StudentScores
group by username;

-- A2：PIVOT 方法
select username as '姓名',[语文],[数学],[英语],[生物] from StudentScores a
PIVOT
(
max(a.score) for a.subject in([语文],[数学],[英语],[生物])
)b;

-- P1：查询项目每个供应商的供给数量？
-- A1：UNPIVOT 方法
select b.projectName,b.supplier,b.supllyNumber from (
select projectName,overseaSupply,nativeSupply,southSupply,northSupply from ProjectDetail) a
UNPIVOT
(
supllyNumber for supplier in(overseaSupply,nativeSupply,southSupply,northSupply)
)b;