MSSQL·PIVOT关键字实现列转行

阅文时长	| 1.03分钟	字数统计	| 1648.8字符
主要内容	| 1、引言&背景 2、PIVOT基本语法结构 3、数据准备&列转行实现 4、声明与参考资料
『MSSQL·PIVOT关键字实现列转行』
编写人	| SCscHero	编写时间	| 2021/8/16 PM11:39
文章类型	| 系列	完成度	| 已完成
座右铭	每一个伟大的事业，都有一个微不足道的开始。

一、引言&背景   完成度：100%

a) 应对问题

如何使用PIVOT关键字实现列转行？

b) 应对场景

例如本博文示例的RBAC的设计方式，常有用户表、用户-角色映射表、角色表三个对象。将其合并成一个表体展示。

二、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实现列转行-用户-角色映射表]

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即可。