【SQL】pivot及unpivot行列转换

 

 pivot 用法

/*
    第一步：创建临时表结构
*/
CREATE TABLE Student  --创建临时表
(
    StuName nvarchar(20),    --学生名称
    StuSubject nvarchar(20),--考试科目
    StuScore int            --考试成绩
)

/*
    第二步：写入测试数据
*/
--张三
INSERT INTO Student(StuName,StuSubject,StuScore) values ('张三','语文',80);  
INSERT INTO Student(StuName,StuSubject,StuScore) values ('张三','数学',75);
INSERT INTO Student(StuName,StuSubject,StuScore) values ('张三','英语',65);
--李四
INSERT INTO Student(StuName,StuSubject,StuScore) values ('李四','语文',36);  
INSERT INTO Student(StuName,StuSubject,StuScore) values ('李四','数学',56);
INSERT INTO Student(StuName,StuSubject,StuScore) values ('李四','英语',38);
--王五
INSERT INTO Student(StuName,StuSubject,StuScore) values ('王五','语文',69);  
INSERT INTO Student(StuName,StuSubject,StuScore) values ('王五','数学',80);
INSERT INTO Student(StuName,StuSubject,StuScore) values ('王五','英语',78);
--赵六
INSERT INTO Student(StuName,StuSubject,StuScore) values ('赵六','语文',80);  
INSERT INTO Student(StuName,StuSubject,StuScore) values ('赵六','数学',80);
INSERT INTO Student(StuName,StuSubject,StuScore) values ('赵六','英语',95);



--查询表
select * from student




select * from student

--pivot 函数语法

--table_source
--PIVOT(
--聚合函数（value_column）
--FOR pivot_column
--IN(<column_list>)
--)

select * from student

--写死列名的方法
select * from student    --数据源是谁
pivot
(
    max(stuscore)     --针对新转变的列名做聚合处理可sum 可max
    for stusubject in (语文,数学,英语)    --for指定哪一列的行内容准备变为新的列名 新的列名在in里面
) a


--行专列加总分，平均分

select m.*,n.总分,n.平均分 from (
select * from student
pivot
(
    max(stuscore)
    for stusubject in (语文,数学,英语)
) a

) as m left join (select stuname,sum(stuScore) as 总分,cast(avg(stuScore*1.0) as decimal(18,2)) as 平均分 from student group by stuname) as n
on m.Stuname = n.Stuname


--动态获取列名的方法
declare @sql varchar(8000)

set @sql=''  --初始化变量@sql

select @sql = @sql+','+ stusubject from student group by stusubject --变量多值赋值

set @sql = stuff(@sql,1,1,'')--去掉首个','

set @sql='select * from student pivot (max(StuScore) for stusubject in ('+@sql+'))a'

exec(@sql)


--动态获取列名的方法，加上总分，平均分

declare @sql varchar(8000)

set @sql=''  --初始化变量@sql

select @sql = @sql+','+ stusubject from student group by stusubject --变量多值赋值

set @sql = stuff(@sql,1,1,'')--去掉首个','

set @sql='select m.*,n.总分,n.平均分 from (
select * from student
pivot
(
    max(stuscore)
    for stusubject in ('+@sql+')
) a

) as m left join (select stuname,sum(stuScore) as 总分,cast(avg(stuScore*1.0) as decimal(18,2)) as 平均分 from student group by stuname) as n
on m.Stuname = n.Stuname'
exec(@sql)

 

 

UNPIVOT用法

-------------
if object_id('tb')is not null drop table tb

go

create table tb(姓名 varchar(10),语文 int,数学 int,物理 int)

insert into tb values('张三',74,83,93)

insert into tb values('李四',74,84,94)

go

select * from tb

go

--UNPIVOT用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现

--完整语法：

--table_source

--UNPIVOT(

--value_column

--FOR pivot_column

--IN(<column_list>)

--)

select * from tb

select * from tb   --数据源
unpivot
(
    分数                             --for前面是新分出来的列名
    for 课程 in (语文,数学,物理)     --for 后面跟的是科目  in 里面是科目的总计类别
) a


----动态获取行列转换表

declare @sql nvarchar(4000)

select @sql=isnull(@sql+',','') + quotename(Name)

from syscolumns

where ID=object_id('tb') and Name not in('姓名')

order by Colid

set @sql='select 姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in('+ @sql + '))b'

exec(@sql)

 

参考自：

https://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html

https://www.cnblogs.com/lwhkdash/archive/2012/06/26/2562979.html