sql 行列转换之关键字pivot，unpivot

试用sql关键字pivot与unpivot可以简便快捷实现数据的行列转换。废话不多说，直接上代码，关于它们的语法请自行msdn。

 

pivot：

 

创建表StudentScores

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [dbo].[StudentScores](  [UserName] [nvarchar](20) NULL,  [Subject] [nvarchar](30) NULL,  [Score] [float] NULL ) ON [PRIMARY]

GO

 

插入数据

insert into StudentScores values('Nick','语文',80) insert into StudentScores values('Nick','数学',90) insert into StudentScores values('Nick','英语',70) insert into StudentScores values('Nick','生物',85) insert into StudentScores values('Kent','语文',80) insert into StudentScores values('Kent','数学',90) insert into StudentScores values('Kent','英语',70)

 

表中数据如下图：

pivot sql语句如下：

select username,语文 as '语文',数学 as '数学',英语 as '英语',生物 as '生物'

from StudentScores

pivot(   

　　max(score)   for [subject] in   (语文,数学,英语,生物)  

)as t

 

一般sql语句：

select username,

max(case [subject] when '语文' then score end ) as '语文',

max(case [subject] when '数学' then score end ) as '数学',

max(case [subject] when '英语' then score end ) as '英语',

max(case [subject] when '生物' then score end ) as '生物'

from StudentScores group by username

 

效果图如下：

unpivot：

unpivot语法与pivot一样

 

创建表StudentScores1

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [dbo].[StudentScores1](  [姓名] [nvarchar](50) NOT NULL,  [语文] [int] NOT NULL,  [数学] [int] NOT NULL,  [英语] [int] NOT NULL,  [生物] [int] NOT NULL ) ON [PRIMARY]

GO

 

插入数据

insert into values('Nick',80,90,70,85)

insert into values('Kent',80,90,70,0)

 

表中数据如下图：

 

执行unpivot sql语句进行列转行如下：

select 姓名,学科,成绩 from StudentScores1

unpivot

(  

　　成绩  for 学科 in  (语文,数学,英语,生物)

)as p

 

一般sql语句：

select 姓名,'语文' as 学科,max(语文)as 成绩 
from StudentScores1 group by 姓名
union all
select 姓名,'数学' as 学科,max(数学)as 成绩 
from StudentScores1 group by 姓名 
union all 
select 姓名,'英语' as 学科,max(英语)as 成绩 
from StudentScores1 group by 姓名 
union all 
select 姓名,'生物' as 学科,max(生物)as 成绩 
from StudentScores1 group by 姓名

 

效果图如下：