SQLSERVER 使用PIVOT、UNPIVOT实现行转列与列转行

一、使用PIVOT实现行转列

1、首先创建一个学生表

CREATE TABLE [dbo].[Students](
[stu_id] [int] IDENTITY(1,1) NOT NULL,
[stu_name] [varchar](100) NULL,
[stu_hobby] [varchar](100) NULL,
[stu_hours] [int] NULL
) ON [PRIMARY]

2、插入几条数据

INSERT INTO [Students](stu_name,stu_hobby,stu_hours)
VALUES ('用户A','唱歌','2')
,('用户A','跳舞','5')
,('用户B','唱歌','5')
,('用户B','跳舞','3')
,('用户C','唱歌','1')
,('用户C','跳舞','5')
,('用户D','游泳','5')
,('用户D','跳舞','5')

3、针对某一列将其转化为列，这里使用到的是stu_hobby

SELECT * FROM (
SELECT stu_name,stu_hobby,stu_hours FROM [Students]
)t
PIVOT(SUM(stu_hours)
FOR stu_hobby IN (唱歌,跳舞,游泳))piv

【效果对比如下：】

（1）转换前：

（2） 转换后：

4、如果我们想要动态把某一行转换成列，并不想每次增加一种爱好都要去手动去改一下sql，我们可以通过声明变量的方式来实现

4.1 这里我们添加一种【健身】的爱好

INSERT INTO [Students](stu_name,stu_hobby,stu_hours)
VALUES ('用户D','健身','5')

4.2 使用声明变量的sql

DECLARE @sql VARCHAR(MAX);
DECLARE @tempField VARCHAR(MAX)=(SELECT STUFF((SELECT ','+stu_hobby FROM [Students] GROUP BY stu_hobby FOR XML PATH('')),1,1,''));
SET @sql='
SELECT * FROM (
SELECT stu_name,stu_hobby,stu_hours FROM [Students]
)t
PIVOT(SUM(stu_hours)
FOR stu_hobby IN ('+@tempField+'))piv
'
EXEC(@sql)

效果图如下：

这样你每添加一种爱好，都会自动地去把这个爱好转换成列，就不用每增加一种，就需要手动在in后面去把爱好补充上去了

二、使用UNPIVOT实现列转行

1、这里是将stu_hobby和stu_name两列转成了行展示

SELECT 转换后字段名,T,stu_hours FROM [Students]
UNPIVOT
(
T FOR 转换后字段名 IN(stu_hobby,stu_name)
)un

【效果对比如下：】

（1）转换前：

（2）转换后：

每日一记，提升自己！！！！