sqlserver行与列的数据互转（pivot、unpivot）

用到的数据库和表：

用到的数据库是ERP_DB

用到的表是Account，Id是主键自增，EmpNo是员工编号，Money是销售额，RecordDate 记录时间。

 

表中的数据：（目前表中只有2和3两个员工）

实现效果 ：每个月的员工的销售总额！（要求行转列显示）

思考：如何获取RecordDate中的月份？

答：用DATENAME函数

select DATENAME(MONTH,RecordDate) from Account a

一、行转列 pivot

首先创建一个临时表用于保存月份

--创建临时表:月份
create table #Months
(
  _month int
)
--声明变量
declare @num int=1
while(@num<=12)
begin
	insert into #Months(_month) values(@num)
	set @num=@num+1
end

写分组查询（按月份分组查询），和临时表进行关联：

select a.EmpNo,b._month,SUM(a.Money) _money from Account a
inner join #Months b
on (DATENAME(MONTH,RecordDate)=b._month)
group by a.EmpNo,b._month

以上分组查出来的数据一定有24条，因为两个员工，每个员工12个月份的数据。

行转列需要用到的函数是pivot，在原本写好的分组语句的基础上再进行修改。

--凡是在select后面出现的列，没有使用聚合函数的，就要出现在group by后面
--分组是有去重复的效果
--数组分组查询出来是24条，两个员工的12个月
select * from(
select a.EmpNo,b._month,SUM(a.Money) _money from Account a
inner join #Months b
on (DATENAME(MONTH,RecordDate)=b._month)
group by a.EmpNo,b._month )as a
pivot(sum(_money) for _month in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) --行转列函数
as Account --取了一个新的表名

效果图：

 

二、列转行 unpivot

先把行转列后的数据放到一个临时表中#Test：

select * into #Test from(
select a.EmpNo,b._month,SUM(a.Money) _money from Account a
inner join #Months b
on (DATENAME(MONTH,RecordDate)=b._month)
group by a.EmpNo,b._month )as a
--第一个参数：聚合函数 最后要分组的列
pivot(sum(_money) for _month in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) --行转列函数
as Account --取了一个新的表名

然后进行列转行：

--2、列转行 unpivot
select * from #Test
--转换为行的值 转换为行对应的列
unpivot([money] for _money in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))
as Test

效果图： 

笔记代码：

use ERP_DB
select * from account
select DATENAME(MONTH,RecordDate) from Account a
--1、行转列 pivot
--创建临时表:月份
create table #Months
(
  _month int
)
--声明变量
declare @num int=1
while(@num<=12)
begin
	insert into #Months(_month) values(@num)
	set @num=@num+1
end

--凡是在select后面出现的列，没有使用聚合函数的，就要出现在group by后面
--分组是有去重复的效果
--数组分组查询出来是24条，两个员工的12个月
select * from(
select a.EmpNo,b._month,SUM(a.Money) _money from Account a
inner join #Months b
on (DATENAME(MONTH,RecordDate)=b._month)
group by a.EmpNo,b._month )as a
pivot(sum(_money) for _month in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) --行转列函数
as Account --取了一个新的表名

select * into #Test from(
select a.EmpNo,b._month,SUM(a.Money) _money from Account a
inner join #Months b
on (DATENAME(MONTH,RecordDate)=b._month)
group by a.EmpNo,b._month )as a
--第一个参数：聚合函数 最后要分组的列
pivot(sum(_money) for _month in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) --行转列函数
as Account --取了一个新的表名
--2、列转行 unpivot
select * from #Test
--转换为行的值 转换为行对应的列
unpivot([money] for _money in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))
as Test

笔记截图：