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

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

 

一般slq语句：

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 姓名

效果图如下：

--动态行转列

declare @sql nvarchar(max)
select @sql=isnull(@sql+'],[','')+科目 from StudentScores1 group by 科目
select @sql='['+@sql+']'
select @sql='select 姓名,'+@sql+' from StudentScores1 a pivot (max(分數) for 科目 in('+@sql+')) as ads'
exec(@sql)

posted on 2012-05-04 09:37 Hao_Guo 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/HaoGuo/archive/2012/05/04/Pivot_Unpivot.html