SQL: PIVOT 数据透视 行列转换

-- 数据透视

-- PIVOT： 行转列
SELECT * FROM
(
    SELECT N‘张三‘ AS 姓名, N‘语文‘ AS 课程,70 AS 分数 UNION
    SELECT N‘张三‘ AS 姓名, N‘数学‘ AS 课程,90 AS 分数 UNION
    SELECT N‘李四‘ AS 姓名, N‘语文‘ AS 课程,85 AS 分数 UNION
    SELECT N‘李四‘ AS 姓名, N‘数学‘ AS 课程,85 AS 分数
) AS A
PIVOT
(
    MAX(分数)
    FOR 课程
    IN(语文, 数学)
) AS B

-- UNPIVOT: 列转行
SELECT 姓名, 课程, 分数 FROM
(
    SELECT N‘张三‘ AS 姓名, 70 AS 语文, 90 AS 数学 UNION
    SELECT N‘李四‘ AS 姓名, 85 AS 语文, 85 AS 数学
) AS B
UNPIVOT
(
    分数
    FOR 课程
    IN(语文, 数学)
) AS A