MSSQL·PIVOT关键字实现列转行
阅文时长 | | 1.03分钟 | 字数统计 | | 1648.8字符 |
主要内容 | | 1、引言&背景 2、PIVOT基本语法结构 3、数据准备&列转行实现 4、声明与参考资料 | ||
『MSSQL·PIVOT关键字实现列转行』 | |||
编写人 | | SCscHero | 编写时间 | | 2021/8/16 PM11:39 |
文章类型 | | 系列 | 完成度 | | 已完成 |
座右铭 | 每一个伟大的事业,都有一个微不足道的开始。 |
一、引言&背景 完成度:100%
a) 应对问题
如何使用PIVOT关键字实现列转行?
b) 应对场景
例如本博文示例的RBAC的设计方式,常有用户表、用户-角色映射表、角色表三个对象。将其合并成一个表体展示。
![](https://img-blog.csdnimg.cn/8d9bc6c165b2490fb810d7e4082b11ea.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzkxNDk5,size_16,color_FFFFFF,t_70)
二、PIVOT基本语法结构 完成度:100%
下面的语法是微软官方总结的如何使用PIVOT操作符。
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
中文版是这样的:
SELECT <非透视列>,
[第一个透视列] AS <column name>,
[第二个透视列] AS <column name>,
...
[最后一个透视列] AS <column name>
FROM
(<结果集>)
AS <源查询的别名>
PIVOT
(
<聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题>]
IN ( [第一个透视列], [第二个透视列],
... [最后一个透视列])
) AS <透视表的别名>
<可选-排序>;
三、数据准备&列转行实现 完成度:100%
a) 数据准备
CREATE TABLE dbo.[实战设计.002.UNPivot实现列转行-用户表]
(
[UID] INT NOT NULL PRIMARY KEY IDENTITY,
[UName] NVARCHAR(20) NULL,
)
CREATE TABLE dbo.[实战设计.002.UNPivot实现列转行-角色表]
(
[RID] INT NOT NULL PRIMARY KEY IDENTITY,
[RName] NVARCHAR(20) NULL,
)
CREATE TABLE dbo.[实战设计.002.UNPivot实现列转行-用户-角色映射表]
(
[MID] INT NOT NULL PRIMARY KEY IDENTITY,
[UID] INT NOT NULL,
[RID] INT NOT NULL
)
INSERT dbo.[实战设计.002.UNPivot实现列转行-用户表](UName)
VALUES(N'张五' -- UName - nvarchar(20)
),
(N'李四' -- UName - nvarchar(20)
),
(N'田七' -- UName - nvarchar(20)
),
(N'王八' -- UName - nvarchar(20)
),
(N'赵九' -- UName - nvarchar(20)
)
INSERT [dbo].[实战设计.002.UNPivot实现列转行-角色表]([RName])
VALUES(N'查看者' -- RName - nvarchar(20)
),
(N'操作者' -- RName - nvarchar(20)
),
(N'删除者' -- RName - nvarchar(20)
)
INSERT [dbo].[实战设计.002.UNPivot实现列转行-用户-角色映射表]([UID], [RID])
VALUES(1, -- UID - int
1 -- RID - int
),
(1, -- UID - int
3 -- RID - int
),
(2, -- UID - int
2 -- RID - int
),
(2, -- UID - int
3 -- RName - int
),
(3, -- UID - int
3 -- RID - int
),
(5, -- UID - int
1 -- RID - int
)
SELECT * FROM dbo.[实战设计.002.UNPivot实现列转行-用户表]
SELECT * FROM dbo.[实战设计.002.UNPivot实现列转行-角色表]
SELECT * FROM dbo.[实战设计.002.UNPivot实现列转行-用户-角色映射表]
![](https://img-blog.csdnimg.cn/e614cafb480747e1a5d62ec1dfef8e77.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzkxNDk5,size_16,color_FFFFFF,t_70)
b) 行转列实现
SELECT T.UID, T.UName,--非透视列
T.查看者, T.操作者, T.删除者--第N个透视列
FROM(SELECT a.UID, a.UName, c.RID, c.RName
FROM [实战设计.002.UNPivot实现列转行-用户表] a
LEFT JOIN [实战设计.002.UNPivot实现列转行-用户-角色映射表] b ON a.UID=b.UID
LEFT JOIN [实战设计.002.UNPivot实现列转行-角色表] c ON b.RID=c.RID) PT--结果集
PIVOT(COUNT(RID)--聚合函数
FOR RName IN([查看者], [操作者], [删除者])) T;--透视列
四、声明与参考资料 完成度:100%
原创博文,未经许可请勿转载。
如有帮助,欢迎点赞、收藏、关注。如有问题,请评论留言!如需与博主联系的,直接博客私信SCscHero即可。