sqlserver行转列与列转行(PIVOT与UNPIVOT)

PIVOT用于将列值旋转为列名（即行转列），在SQLServer 2000可以用聚合函数配合CASE语句实现

PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P

完整语法：

table_source

PIVOT(

聚合函数（value_column）

FOR pivot_column

IN(<column_list>)

)

?

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

完整语法：

table_source

UNPIVOT(

value_column

FOR pivot_column

IN(<column_list>)

)

--*************************************************************************************
--**********************************行转列*********************************************
--*************************************************************************************
create table course  
(  
    id int identity(1,1),--递增1,stuNo varchar(50),courseName varchar(50),courseScore decimal   
)  

insert into course values('02','思想政治','85.5'),('02','数学','70'),'语文','80'),'物理','90'),'化学','65'),'英语','96')  
                           
insert into course values('03','60'),('03','84'),'76'),'54')   
select * from course

--方法一：
select ROW_NUMBER() over(order by stuNo asc) as ID,stuNo as '学号',max(case courseName when '思想政治' then courseScore else 0 end) as '思想政治',max(case courseName when '数学' then courseScore else 0 end) as '数学',max(case courseName when '语文' then courseScore else 0 end) as '语文',max(case courseName when '物理' then courseScore else 0 end) as '物理',max(case courseName when '化学' then courseScore else 0 end) as '化学',max(case courseName when '英语' then courseScore else 0 end) as '英语'
from course group by stuNo

--方法二:
select ROW_NUMBER() over(order by a.stuNo asc) as ID,a.stuNo as '学号',MAX(a.思想政治) as '思想政治',MAX(a.数学) as '数学',MAX(a.语文) as '语文',MAX(a.物理) as '物理',MAX(a.化学) as '化学',MAX(a.英语) as '英语'
from course pivot(max(courseScore) for courseName in(思想政治,数学,语文,物理,化学,英语))a 
group by a.stuNo

--*************************************************************************************
--**********************************列转行*********************************************
--*************************************************************************************
create table course1  
(  
    
    ID int identity(1,学号 varchar(50),思想政治 int,数学 int,语文 int,物理 int,化学 int,英语 int
)  
go
select * from course1
insert into course1 
select '02',86,70,80,90,65,96
union all 
select '03',60,84,76,54
go

select * from course1
--方法一:
select ROW_NUMBER() over(order by t.stuNo asc) as id,t.stuNo,t.courseName,t.courseScore from
(
	select 学号 as stuNo,courseName='思想政治',courseScore=思想政治 from course1
	union all
	select 学号 as stuNo,courseName='数学',courseScore=数学 from course1
	union all
	select 学号 as stuNo,courseName='语文',courseScore=语文 from course1
	union all
	select 学号 as stuNo,courseName='物理',courseScore=物理 from course1
	union all
	select 学号 as stuNo,courseName='化学',courseScore=化学 from course1
	union all
	select 学号 as stuNo,courseName='英语',courseScore=英语 from course1
) t order by t.stuNo,case t.courseName when '思想政治' then 1
									   when '数学' then 2
									   when '语文' then 3
									   when '物理' then 4
									   when '化学' then 5
									   when '英语' then 6 end
									   

--方法二:									   
select ROW_NUMBER() over(order by a.学号 asc) as id,a.学号 as stuNo,a.courseName,a.courseScore from course1 unpivot(courseScore for courseName in(思想政治,英语))a

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!