1.概述
在SQLServer中,有时需要将数据表中的行与列进行转换,以便更好地进行数据分析和统计。行列转换可以实现以下功能:
将一列数据按行转换为多列数据
将一行数据按列转换为多行数据
将表格进行透视操作
本文将介绍如何在SQLServer中使用特定的查询语句实现行列转换。
2.使用PIVOT实现行列转换
2.1 PIVOT概述
PIVOT是SQLServer中用于实现行列转换的关键字,该关键字可以将一列数据按照指定的列进行转置,并将多行转化为多列。
2.2 PIVOT语法
PIVOT语法如下:
SELECT
[列字段1],
[列字段2],
...
FROM
[原始数据表]
PIVOT
(
[聚合函数](值字段) FOR [列字段] IN
(
[列值1],
[列值2],
...
)
) AS [结果数据表别名]
其中,[列字段1][列字段2]表示需要保留的非转置列字段,[原始数据表]为需要进行行列转换的数据表名称,[聚合函数](值字段)为在转换后的数据表中需要使用的聚合函数,[列字段]为需要被转置的列名称,[列值1][列值2]为需要转置的值列表,[结果数据表别名]为转化后的数据表别名。
2.3 PIVOT实例
下面,我们以一个学校考试成绩数据表为例,演示如何使用PIVOT实现行列转换。 原始数据表如下所示:
ID | 姓名 | 科目 | 分数 |
1 | 张三 | 语文 | 90 |
2 | 李四 | 语文 | 80 |
3 | 王五 | 语文 | 70 |
4 | 张三 | 数学 | 66 |
5 | 李四 | 数学 | 78 |
6 | 王五 | 数学 | 88 |
现在需要将上述数据按照姓名进行行列转换,转换结果如下所示:
姓名 | 语文 | 数学 |
张三 | 90 | 66 |
李四 | 80 | 78 |
王五 | 70 | 88 |
使用PIVOT实现上述转换,完整代码如下所示:
SELECT
姓名,
[语文],
[数学]
FROM
(
SELECT
姓名,
科目,
分数
FROM
考试成绩表
) AS p
PIVOT
(
MAX(分数)
FOR 科目 IN ([语文], [数学])
) AS pvt
代码解释如下:
第1-3行:选择需要返回的列字段。
第5-10行:使用
第6行:指定需要进行聚合函数操作的值字段。
第7-9行:指定需要保留的非转置列字段。
第10行:指定需要进行转换的列与需要转换的列值列表。
第11行:指定转换后的数据表别名。
3.使用UNPIVOT实现行列转换
3.1 UNPIVOT概述
UNPIVOT是SQLServer中用于将多列数据转换为一列数据的关键字,该关键字可以将多列数据中的值进行解析,并将其转换为单独的一列。
3.2 UNPIVOT语法
UNPIVOT语法如下:
SELECT
[列字段], [值]
FROM
(
SELECT
[列字段1], [列字段2], ...
FROM
[原始数据表]
) AS p
UNPIVOT
(
[值]
FOR [列字段] IN
(
[列值1], [列值2], ...
)
) AS [结果数据表别名]
其中,[列字段], [值]表示需要返回的列名称,[列字段1], [列字段2], ...为需要被转换为列的字段列表,[原始数据表]为需要进行行列转换的数据表名称,[值]为转换后的值字段,[列字段]为被转换的列名称,[列值1], [列值2], ...为被转换的列值列表,[结果数据表别名]为转化后的数据表别名。
3.3 UNPIVOT实例
下面,我们以一个学校考试成绩数据表为例,演示如何使用UNPIVOT实现行列转换。原始数据表如下所示:
ID | 姓名 | 语文 | 数学 |
1 | 张三 | 90 | 66 |
2 | 李四 | 80 | 78 |
3 | 王五 | 70 | 88 |
现在需要将上述数据按照姓名进行行列转换,转换结果如下所示:
姓名 | 科目 | 分数 |
张三 | 语文 | 90 |
张三 | 数学 | 66 |
李四 | 语文 | 80 |
李四 | 数学 | 78 |
王五 | 语文 | 70 |
王五 | 数学 | 88 |
使用UNPIVOT实现上述转换,完整代码如下所示:
SELECT
姓名,
科目,
分数
FROM
(
SELECT
ID,
姓名,
语文,
数学
FROM
考试成绩表
) AS p
UNPIVOT
(
分数
FOR 科目 IN (语文, 数学)
) AS pvt
代码解释如下:
第1-3行:选择需要返回的列字段。
第5-9行:使用UNPIVOT关键字将数据表进行列行转换。
第6行:指定需要进行解析的列字段列表。
第7-9行:指定需要将列转换为行的列值列表。
第10行:指定转换后的数据表别名。
4.总结
行列转换是SQLServer中常用的数据操作,能够将数据表中的行与列进行转换,以便更好地进行数据分析和统计。本文介绍了在SQLServer中使用PIVOT和UNPIVOT两个关键字实现行列转换的方法,并通过具体实例进行了演示。在使用行列转换时,需要根据实际情况选择合适的方法,以保证数据的准确性和完整性。