粘贴行列如何转换_sql实现行转列

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全家桶1年46，售后保障稳定

假如现在我们有这样一张表，命名为TB_STUDENT_SCORE

表中内容为：

粘贴行列如何转换_sql实现行转列

可能会用到pivot的常见情况是：需要生成交叉表格报表以汇总数据。例如，假设需要在 TB_STUDENT_SCORE表中中查询 学生姓名，各科成绩，总分，平均分，名称以生成报表。

如下图所示：

粘贴行列如何转换_sql实现行转列

以下是带批注的 PIVOT 语法。

SELECT <非透视的列>,

[第一个透视的列] AS <列名称>,

[第二个透视的列] AS <列名称>,

…

[最后一个透视的列] AS <列名称>,

FROM

(<生成数据的 SELECT 查询>)

AS <源查询的别名>

PIVOT

(

<聚合函数>(<要聚合的列>)

FOR

[<包含要成为列标题的值的列>]

IN ( [第一个透视的列], [第二个透视的列],

… [最后一个透视的列])

) AS <透视表的别名>

<可选的 ORDER BY 子句>;

pivot_column 和 value_column 是 PIVOT 运算符使用的分组列。PIVOT 遵循以下过程获得输出结果集：

对分组列的 input_table 执行 GROUP BY，为每个组生成一个输出行。
输出行中的分组列获得 input_table 中该组的对应列值。
通过执行以下操作，为每个输出行生成列列表中的列的值：
1. 针对 pivot_column，对上一步在 GROUP BY 中生成的行另外进行分组。
 对于 column_list 中的每个输出列，选择满足以下条件的子组：
 
 pivot_column = CONVERT(<data type of pivot_column>, 'output_column')
2. 针对此子组上的 aggregate_function 对 value_column 求值，其结果作为相应的 output_column 的值返回。如果该子组为空，SQL Server 将为该 output_column 生成 Null 值。如果聚合函数是 COUNT，且子组为空，则返回零 (0)。

在上述列子中，我们可以用以下SQL完成其功能
1.首先使用PIVOT进行行列转换

SELECT STUDENT_NAME, CET.[A],CET.[B],CET.[C],CET.[D] FROM TB_STUDENT_SCORE PIVOT (AVG(STUDENT_SCORE) FOR STUDENT_SUBJECT IN([A],[B],[C],[D])) AS CET

2.使用开窗函数统计学生成绩
SELECT T1.*, AVG(T2.STUDENT_SCORE) OVER (PARTITION BY T2.STUDENT_NAME) AS ‘平均成绩’ ,SUM(T2.STUDENT_SCORE) OVER (PARTITION BY T2.STUDENT_NAME) AS ‘总分’ FROM ( SELECT STUDENT_NAME, CET.[A],CET.[B],CET.[C],CET.[D] FROM TB_STUDENT_SCORE PIVOT (AVG(STUDENT_SCORE) FOR STUDENT_SUBJECT IN([A],[B],[C],[D])) AS CET ) T1 LEFT JOIN TB_STUDENT_SCORE T2 ON T1.STUDENT_NAME=T2.STUDENT_NAME

3.去除重复行
SELECT DISTINCT * FROM( SELECT T1.*, AVG(T2.STUDENT_SCORE) OVER (PARTITION BY T2.STUDENT_NAME) AS ‘平均成绩’ ,SUM(T2.STUDENT_SCORE) OVER (PARTITION BY T2.STUDENT_NAME) AS ‘总分’ FROM ( SELECT STUDENT_NAME, CET.[A],CET.[B],CET.[C],CET.[D] FROM TB_STUDENT_SCORE PIVOT (AVG(STUDENT_SCORE) FOR STUDENT_SUBJECT IN([A],[B],[C],[D])) AS CET ) T1 LEFT JOIN TB_STUDENT_SCORE T2 ON T1.STUDENT_NAME=T2.STUDENT_NAME ) T3

最终，得到
粘贴行列如何转换_sql实现行转列

发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/210093.html原文链接：https://javaforall.cn

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权正版激活】： 官方授权正版激活支持Jetbrains家族下所有IDE 使用个人JB账号...