侧边栏壁纸
博主头像
IT技术分享社区博主等级

一个有态度的互联网技术交流平台

  • 累计撰写 36 篇文章
  • 累计创建 13 个标签
  • 累计收到 15 条评论

目 录CONTENT

文章目录

数据库:SQLServer 实现行转列、列转行用法笔记

IT技术分享社区
2022-07-03 / 0 评论 / 1 点赞 / 59 阅读 / 1,201 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-07-03,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

数据库:SQLServer 实现行转列、列转行用法笔记

在许多的互联网项目当中,报表开发是整个项目当中很重要的一个功能模块。其中会有一些比较复杂的报表统计需要行转列或者列转行的需求。今天给大家简单介绍一下在SQLServer当中如何使用PIVOT、UNPIVOT内置函数实现数据报表的行转列、列转行。有需要的朋友可以一起学习一下。

一、PIVOT、UNPIVOT用途
官方解释:可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。

注意:UNPIVOT运算符通过将列旋转到行来执行PIVOT的反向操作,UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 执行聚合,并将多个可能的行合并为输出中的一行。UNPIVOT 不重现原始表值表达式的结果,因为行已被合并。另外,UNPIVOT 输入中的 NULL 值也在输出中消失了。如果值消失,表明在执行 PIVOT 操作前,输入中可能就已存在原始 NULL 值。

二、PIVOT语法格式
SELECT <非透视的列>,

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

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

...

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

FROM

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

AS <源查询的别名>

PIVOT

(

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

FOR

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

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

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

) AS <透视表的别名>

<可选的 ORDER BY 子句>;

三、行转列示例说明

-- 创建测试表 学习成绩统计表
CREATE  TABLE ScoreStatistics
(
   UserName         NVARCHAR(20),        --学生姓名
   SubjectName       NVARCHAR(30),        --科目名称
   Score            FLOAT,               --成绩
)
-- 插入测试数据
INSERT INTO ScoreStatistics SELECT '小王', '语文', 100
INSERT INTO ScoreStatistics SELECT '小王', '数学', 90.5
INSERT INTO ScoreStatistics SELECT '小王', '英语', 88
INSERT INTO ScoreStatistics SELECT '小王', '历史', 65
INSERT INTO ScoreStatistics SELECT '小李', '语文', 81
INSERT INTO ScoreStatistics SELECT '小李', '数学', 99
INSERT INTO ScoreStatistics SELECT '小李', '英语', 95
INSERT INTO ScoreStatistics SELECT '小李', '历史', 90
INSERT INTO ScoreStatistics SELECT '小刘', '语文', 90
INSERT INTO ScoreStatistics SELECT '小刘', '数学', 85
INSERT INTO ScoreStatistics SELECT '小刘', '英语', 59
INSERT INTO ScoreStatistics SELECT '小刘', '历史', 98
-- 传统写法
select UserName,
 max(case SubjectName when '语文' then Score else 0 end)语文,
 max(case SubjectName when '数学'then Score else 0 end)数学,
 max(case SubjectName when '英语'then Score else 0 end)英语,
 max(case SubjectName when '历史'then Score else 0 end)历史
from ScoreStatistics
group by UserName
-- PIVOT 写法更简洁
SELECT * FROM ScoreStatistics
AS P
PIVOT
(
    SUM(Score/*行转列后 列的值*/) FOR
    p.SubjectName/*需要行转列的列*/ IN ([语文],[数学],[英语],历史
    /*列的值*/)
) AS T
-- order by 语文 desc  具体科目排序
-- order by username desc -- 姓名排序
-- 动态拼接列的示例
DECLARE @sql_str VARCHAR(8000); -- 要执行的sql
--拿到数值列 [历史],[数学],[英语],[语文]
DECLARE @sql_col VARCHAR(8000);
SELECT @sql_col = ISNULL(@sql_col + ',','') 
+ QUOTENAME(SubjectName) 
FROM ScoreStatistics GROUP BY SubjectName;
print(@sql_col); -- 打印数值列,不必需
SET @sql_str = '
SELECT * FROM (
SELECT [UserName],[SubjectName],[Score] FROM [ScoreStatistics]) 
p PIVOT
(SUM([Score]) FOR [SubjectName] IN ( '+ @sql_col +') ) AS pvt
ORDER BY pvt.[UserName]'
PRINT (@sql_str);--打印执行的sql
EXEC (@sql_str);-- 执行查询

输出结果:

UserName 语文 数学 英语 历史

小王 100 90.5 88 65

小刘 90 85 59 98

小李 81 99 95 90
四、列转行示例


-- 插入测试表
CREATE  TABLE ScoreSummary
(
   UserName         NVARCHAR(20),        --学生姓名
   数学        FLOAT,               --数学成绩
   英语             FLOAT,               --英语成绩
   语文             FLOAT,               --语文成绩
   历史             FLOAT,               --历史成绩
)
-- 插入测试数据
INSERT INTO ScoreSummary SELECT '小李',81,99,95,90;
INSERT INTO ScoreSummary SELECT '小刘',90,85,59,98;
INSERT INTO ScoreSummary SELECT '小王',100,90.5,88,65;
-- 查询用法
select aa.UserName,aa.Score
from (select UserName,数学,英语,语文,历史 from dbo.ScoreSummary) as a
unpivot(
Score for ScoreSummary in
(数学,英语,语文,历史)
) as aa order by aa.UserName

输出结果:
UserName Score

小李 81

小李 99

小李 95

小李 90

小刘 90

小刘 85

小刘 59

小刘 98

小王 100

小王 90.5

小王 88

小王 65

1

评论区