T-SQL——数据透视和逆透视，java 架构师面试答案

SELECT '张三' AS Name, '语文' AS Subject, 100 AS Scores

UNION ALL

SELECT '张三' AS Name, '数学' AS Subject, 90 AS Scores

UNION ALL

SELECT '张三' AS Name, '英语' AS Subject, 80 AS Scores

UNION ALL

SELECT '李四' AS Name, '语文' AS Subject, 90 AS Scores

UNION ALL

SELECT '李四' AS Name, '数学' AS Subject, 70 AS Scores

UNION ALL

SELECT '李四' AS Name, '英语' AS Subject, 60 AS Scores

)

SELECT * FROM Temp1

结果：

Name Subject Scores

张三 语文 100

张三 数学 90

张三 英语 80

李四 语文 90

李四 数学 70

李四 英语 60

【测试数据 2】

WITH Temp2 AS

(

SELECT '张三' AS Name, '八年级二班' AS Class, NULL AS 英语, NULL AS 数学, 100 AS 语文

UNION ALL

SELECT '李四' AS Name, '三年级二班' AS Class, 60 AS 英语, 70 AS 数学, 90 AS 语文

UNION ALL

SELECT '张三' AS Name, '三年级二班' AS Class, 80 AS 英语, 90 AS 数学, NULL AS 语文

)

SELECT * FROM Temp2

结果：

Name Class 英语 数学 语文

张三 八年级二班 NULL NULL 100

李四 三年级二班 60 70 90

张三 三年级二班 80 90 NULL

0.2 对一维表和二维表理解

测试数据 1：

• 称为：一维表

• 特征：每条记录的特定字段可能出现值重复

• 一维表是数据记录的最原始的样子。

• 一维表包含一列字段表示每条记录的属性。比如说测试数据 1 中的 Subject 列

• 一维表的字段名称即表示该字段下的数据的实际含义。比如说测试数据 1 中的 Scores 列下的数据表示成绩

测试数据 2：

• 称为：二维表

• 特征：列字段名称就是种类信息，每条记录的

• 二维表则是展示出分类汇总后的数据信息

• 二维表若无说明，是无法明确数据含义的，比如测试数据 2，若是不告诉你这是成绩，你是无法理解数据值的真实含义的

一维表和二维表对比：

• 按照数据库第一范式：一个实体中不应该包含重复类型的特性，对应到表就是：类似的值不能在一个表的多个列上重复

• 创建表应该按照一维表的格式创建，记录产生信息。

• 若是建表的时候创建为二维表，将所有的种类做为字段，则可能出现表的字段非常的多，当种类有所添加的时候，后续还要继续添加字段。

• 一维表方便存储数据，同样也是便于后期查询

• 二维表可以直观的表现数据的信息，是用于展示的报表和报告

1. 透视转换

1.1 使用标准 SQL 进行数据透视

--注意这里实现业务场景，在 CASE WHEN 语句中

--我们应该使用默认的 ELSE NULL ,而不是 ELSE 0。没有数据是没有数据，0 是 0

SELECT Name,

SUM(CASE WHEN Subject ='语文' THEN Temp1.Scores ElSE NULL END) AS 语文 ,

SUM(CASE WHEN Subject ='数学' THEN Temp1.Scores ELSE NULL END) AS 数学 ,

SUM(CASE WHEN Subject ='英语' THEN Temp1.Scores ELSE NULL END) AS 英语

FROM Temp1 GROUP BY Temp1.Name

结果：

Name 英语 数学 语文

李四 60 70 90

张三 80 90 100

【说明】：使用标准的 SQL 语句进行数据的透视转换，我们可以直白的发现透视转换，是分为三个阶段的：

分组-->扩展-->聚合

以上例说明：

• 分组：我们按照 Name 字段进行分组，使每一个用户产生一条记录

• 使用 GROUP BY 实现分组

• 扩展：扩展是对列的扩展，使用 case 语句， 对三个科目（语文，数学，英语）扩展为列

• 使用 SELECT 配合 CASE WHEN 实现

• 聚合：按照分组依据进行聚合，这里按照 Name 字段分组，使用 SUM 函数对 Scores 字段聚合

• 使用任一聚合函数聚合

对照上述三个逻辑步骤：

• Name 字段：分组依据字段（分组元素）。指定要用于透视结果表的左侧列，也称为设置键

• Subject 字段：待扩展字段（待扩展元素）。透视结果表中的列头所在的列，也称为透视键，"语文，数学，英语"为目标列的列名的集合，又称为透视输出列

• Scores 字段：聚合字段（聚合元素）。透视结果表中的主要显示的数据就是来自这个字段的值，又称为透视值

1.2 使用 T-SQL 中 pivot 函数进行数据透视

【函数】：pivot

【作用】：行转列函数，SQL Server2005

【语法】：pivot(任一聚合函数 for 需转列的值所在列名 in (需转为列名的值))

【用法】：select …from table pivot(……) as T

【示例 1】：

现有一张成绩表，表中记录了每个学生每科目的成绩，如下。

现在需要将科目作为列头，统计每个学生的成绩。

WITH Temp1 AS

(

SELECT '张三' AS Name, '语文' AS Subject, 100 AS Scores

UNION ALL

SELECT '张三' AS Name, '数学' AS Subject, 90 AS Scores

UNION ALL

SELECT '张三' AS Name, '英语' AS Subject, 80 AS Scores

UNION ALL

SELECT '李四' AS Name, '语文' AS Subject, 90 AS Scores

UNION ALL

SELECT '李四' AS Name, '数学' AS Subject, 70 AS Scores

UNION ALL

SELECT '李四' AS Name, '英语' AS Subject, 60 AS Scores

)

SELECT T.Name, T.英语, T.数学, T.语文 FROM Temp1 PIVOT(SUM(Scores) FOR [Subject] IN (语文,数学,英语)) T;

结果：

Name 英语 数学 语文

李四 60 70 90

张三 80 90 100

【注意 1】：

我们是按照以下格式：

select …from table pivot(任意聚合函数(聚合字段) for 待扩展字段名 in (待扩展元素集)) as T

使用 pivot 对 table 进行数据透视

若是 table 中的字段既不是聚合字段，又不是待扩展字段，则会默认为 pivot 函数中的分组依据的字段。

所以使用 pivot 函数进行数据透视的结果集一般不会是数据库中的原表，而是一个查询出的特定字段的结果集

【注意 2】：

若待扩展元素集是非常规标识符，比如说日期或数字开头的其他类型数据，则我们需要使用方括号将每一个待扩展展元素括起。

【示例 2】：

现在在测试数据 1 中添加一个 Class 字段，使用 pivot 函数透视数据

WITH Temp1 AS

(

SELECT '张三' AS Name, '语文' AS Subject, 100 AS Scores,'八年级一班' AS Class

UNION ALL

SELECT '张三' AS Name, '数学' AS Subject, 90 AS Scores,'三年级二班' AS Class

UNION ALL

SELECT '张三' AS Name, '英语' AS Subject, 80 AS Scores,'三年级二班' AS Class

UNION ALL

SELECT '李四' AS Name, '语文' AS Subject, 90 AS Scores,'三年级二班' AS Class

UNION ALL

SELECT '李四' AS Name, '数学' AS Subject, 70 AS Scores,'三年级二班' AS Class

UNION ALL

SELECT '李四' AS Name, '英语' AS Subject, 60 AS Scores,'三年级二班' AS Class

)

SELECT T.Name,T.Class, T.英语, T.数学, T.语文 FROM Temp1 PIVOT(SUM(Scores) FOR [Subject] IN (语文,数学,英语)) T;

结果：

Name Class 英语 数学 语文

张三 八年级一班 NULL NULL 100

李四 三年级二班 60 70 90

张三 三年级二班 80 90 NULL

【说明】：结果集 Temp1 中的 class 既不是聚合字段，也不是待扩展字段，所以会被默认为是分组依据！

而往往，我们期望的分组依据只要 Name 字段，

所以一般都是建议不要直接对数据库中的基础表进行透视操作，而是将特定字段查询出来作为一个表结果集，之后在对这个结果集进行透视操作。

1.3 关于 待扩展元素集合获取的方式

• 见：T-SQL——透视PIVOT动态获取待扩展元素集
  

2. 逆透视转换

【逆透视】：逆透视转换将来自单个记录中多个列的值扩展为单个列中具有同样值的多个记录，使得非规范的数据集成为较规范的版本。

2.1 使用标准 SQL 进行数据逆透视

数据的逆透视也是分为三步：请客，斩首，收下当狗

啊唾，不对，应该是是：生成副本-->提取元素-->删除不相干交叉

WITH Temp AS

(

--测试数据集

SELECT '张三' AS Name, '八年级二班' AS Class, NULL AS 英语, NULL AS 数学, 100 AS 语文

UNION ALL

SELECT '李四' AS Name, '三年级二班' AS Class, 60 AS 英语, 70 AS 数学, 90 AS 语文

UNION ALL

SELECT '张三' AS Name, '三年级二班' AS Class, 80 AS 英语, 90 AS 数学, NULL AS 语文

)

,Temp1 AS

(