SQL Server中行列转换 Pivot UnPivot

转载 2011年01月11日 10:37:00

SQL Server中行列转换 Pivot UnPivot

PIVOT 用于将列值旋转为列名(即行转列),在SQL Server 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>)

)

 

注意:PIVOT、UNPIVOT是SQL Server 2005 的语法,使用需修改数据库 兼容级别
 
在数据库属性->选项->兼容级别改为   90

 

典型实例

一、行转列

1 、建立表格

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

go

create table tb( 姓名 varchar ( 10), 课程 varchar ( 10), 分数 int )

insert into tbvalues ( ' 张三 ' , ' 语文 ' , 74)

insert into tbvalues ( ' 张三 ' , ' 数学 ' , 83)

insert into tbvalues ( ' 张三 ' , ' 物理 ' , 93)

insert into tbvalues ( ' 李四 ' , ' 语文 ' , 74)

insert into tbvalues ( ' 李四 ' , ' 数学 ' , 84)

insert into tbvalues ( ' 李四 ' , ' 物理 ' , 94)

go

select * from tb

go

姓名         课程         分数

---------- ---------- -----------

张三         语文         74

张三         数学         83

张三         物理         93

李四         语文         74

李四         数学         84

李四         物理         94

 

2 、使用SQL Server 2000静态SQL

--c

select 姓名 ,

 max ( case 课程 when ' 语文 ' then 分数 else 0end) 语文 ,

 max ( case 课程 when ' 数学 ' then 分数 else 0end) 数学 ,

 max ( case 课程 when ' 物理 ' then 分数 else 0end) 物理

from tb

group by 姓名

姓名         语文          数学          物理

---------- ----------- ----------- -----------

李四         74          84          94

张三         74          83          93

 

3 、使用SQL Server 2000动态SQL

--SQL SERVER 2000 动态 SQL, 指课程不止语文、数学、物理这三门课程。 ( 以下同 )

-- 变量按 sql 语言顺序赋值

declare @sqlvarchar ( 500)

set @sql= 'select 姓名 '

select @sql= @sql+ ',max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end)[' + 课程 + ']'

from ( select distinct 课程 from tb)a-- from tb group by 课程,默认按课程名排序

set @sql= @sql+ ' from tb group by 姓名 '

exec ( @sql)

 

-- 使用 isnull(), 变量先确定动态部分

declare @sqlvarchar ( 8000)

select @sql= isnull ( @sql+ ',' , '' ) + ' max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'

from ( select distinct 课程 from tb)as a      

set @sql= 'select 姓名 ,' + @sql+ ' from tb group by 姓名 '

exec ( @sql)

姓名         数学          物理          语文

---------- ----------- ----------- -----------

李四         84          94          74

张三         83          93          74

 

4 、使用SQL Server 2005静态SQL

select * from tb pivot ( max ( 分数 ) for 课程 in ( 语文 , 数学 , 物理 )) a

 

5 使用SQL Server 2005动态SQL

-- 使用 stuff()

declare @sqlvarchar ( 8000)

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

select @sql= @sql+ ',' + 课程 from tbgroupby 课程 -- 变量多值赋值

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

set @sql= 'select * from tb pivot (max( 分数 ) for 课程 in (' + @sql+ '))a'

exec ( @sql)

 

-- 或使用 isnull()

declare @sqlvarchar ( 8000)

–- 获得课程集合

select @sql= isnull ( @sql+ ',' , '' ) + 课程 from tbgroupby 课程            

set @sql= 'select * from tb pivot (max( 分数 ) for 课程 in (' + @sql+ '))a'

exec ( @sql)

 

二、行转列结果加上总分、平均分

1 、使用SQL Server 2000静态SQL

--SQL SERVER 2000 静态 SQL

select 姓名 ,

max ( case 课程 when ' 语文 ' then 分数 else 0end ) 语文 ,

max ( case 课程 when ' 数学 ' then 分数 else 0end ) 数学 ,

max ( case 课程 when ' 物理 ' then 分数 else 0end ) 物理 ,

sum ( 分数 ) 总分 ,

cast ( avg ( 分数 * 1.0) as decimal ( 18, 2)) 平均分

from tb

group by 姓名

姓名         语文          数学          物理          总分          平均分

---------- ----------- ----------- ----------- -----------

李四         74          84          94          252         84.00

张三         74          83          93          250         83.33

 

2 、使用SQL Server 2000动态SQL

--SQL SERVER 2000 动态 SQL

declare @sqlvarchar ( 500)

set @sql= 'select 姓名 '

select @sql= @sql+ ',max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end)[' + 课程 + ']'

from ( select distinct 课程 from tb) a

set @sql= @sql+ ',sum( 分数 ) 总分 ,cast(avg( 分数 *1.0) as decimal(18,2))       平均分 from tb group by 姓名 '

exec ( @sql)

 

3 、使用SQL Server 2005静态SQL

select m.*, n. 总分 , n. 平均分

from

( select * from tb pivot( max ( 分数 ) for 课程 in( 语文 , 数学 , 物理 )) a) m,

( select 姓名 , sum ( 分数 ) 总分 , cast ( avg ( 分数 * 1.0) as decimal ( 18, 2)) 平均分

from tb

group by 姓名 ) n

where m. 姓名 = n. 姓名

 

4 、使用SQL Server 2005动态SQL

-- 使用 stuff()

--

declare @sqlvarchar ( 8000)

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

select @sql= @sql+ ',' + 课程 from tbgroupby 课程 -- 变量多值赋值

-- select @sql = @sql + ','+ 课程 from (select distinct 课程 from tb)a

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

set @sql= 'select m.* , n. 总分 ,n. 平均分 from

(select * from (select * from tb) a pivot (max( 分数 ) for 课程 in (' + @sql+ ')) b) m ,

(select 姓名 ,sum( 分数 ) 总分 , cast(avg( 分数 *1.0) as decimal(18,2)) 平均分 from tb group by 姓名 ) n

where m. 姓名 = n. 姓名 '

exec ( @sql)

 

-- 或使用 isnull()

declare @sqlvarchar ( 8000)

select @sql= isnull ( @sql+ ',' , '' ) + 课程 from tbgroup by 课程

set @sql= 'select m.* , n. 总分 ,n. 平均分 from

(select * from (select * from tb) a pivot (max( 分数 ) for 课程 in (' +

 @sql+ ')) b) m ,

(select 姓名 ,sum( 分数 ) 总分 , cast(avg( 分数 *1.0) as decimal(18,2)) 平均分 from tb group by 姓名 ) n

where m. 姓名 = n. 姓名 '

exec ( @sql)

 

二、列转行

1 、建立表格

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

go

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

insert into tbvalues ( ' 张三 ' , 74, 83, 93)

insert into tbvalues ( ' 李四 ' , 74, 84, 94)

go

select * from tb

go

姓名         语文          数学          物理

---------- ----------- ----------- -----------

张三        74          83          93

李四         74          84          94

 

2 、使用SQL Server 2000静态SQL

--SQL SERVER 2000 静态 SQL

select *from

(

 select 姓名 , 课程 = ' 语文 ' , 分数 = 语文 from tb

 unionall

 select 姓名 , 课程 = ' 数学 ' , 分数 = 数学 from tb

 unionall

 select 姓名 , 课程 = ' 物理 ' , 分数 = 物理 from tb

) t

order by 姓名 ,case 课程 when ' 语文 ' then1 when ' 数学 ' then2 when ' 物理 ' then3 end

姓名         课程   分数

---------- ---- -----------

李四         语文  74

李四         数学  84

李四         物理  94

张三         语文  74

张三         数学  83

张三         物理  93

  

2 、使用SQL Server 2000动态SQL

--SQL SERVER 2000 动态 SQL

-- 调用系统表动态生态。

declare @sqlvarchar ( 8000)

select @sql= isnull ( @sql+ ' union all ' , '' ) + ' select 姓名 , [ 课程 ]='

+ quotename ( Name , '''' ) + ' , [ 分数 ] = ' + quotename ( Name ) + ' from tb'

from syscolumns

where Name != ' 姓名 ' and ID= object_id ( 'tb' ) -- 表名 tb ,不包含列名为姓名的其他列

order by colid

exec ( @sql+ ' order by 姓名 ' )

go

 

3 、使用SQL Server 2005静态SQL

--SQL SERVER 2005 动态 SQL

select 姓名 , 课程 , 分数 from tb unpivot ( 分数 for 课程 in ([ 语文 ] ,[ 数学 ] ,[ 物理 ] )) t

 

4 、使用SQL Server 2005动态SQL

--SQL SERVER 2005 动态 SQL

declare @sqlnvarchar ( 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)

SQL Server 2005之PIVOT/UNPIVOT行列转换(行转列、列转行)

SQL Server2005引入了很多迎合开发者口味的新特性,虽然改动不大,却大大了减少了开发者的工作量,这种替用户考虑的开发思路,值得称赞。 在SQL Server2000中,要实现行列转换,需要...
  • wozengcong
  • wozengcong
  • 2015年01月07日 13:05
  • 2740

SQL Server 2008中的Pivot和UnPivot

SQL Server 2008中SQL应用系列--目录索引今天给新成员讲解PIVOT 和 UNPIVOT示例,顺便整理了一下其用法。这是自SQL Server 2005起提供的新功能。官方示例:htt...
  • downmoon
  • downmoon
  • 2012年04月05日 18:56
  • 16785

Oracle 11g 行列互换 pivot 和 unpivot 说明

针对Oracle 11g 之前版本的行列转换,之前整理过一篇文档:Oracle 行列转换 总结http://blog.csdn.net/tianlesoftware/article/details/4...
  • tianlesoftware
  • tianlesoftware
  • 2011年12月10日 22:04
  • 32565

SQLServer行列转换PIVOT函数中聚合函数的使用意义及选择

PIVOT函数中必须要包含一个聚合函数,聚合函数分别是:COUNT、SUM、MAX、MIN、AVG,针对不同的使用场景,该选择哪个聚合函数,意义都是不一样的,下面还是举例说明吧! 我建了如下一张销售...
  • Wikey_Zhang
  • Wikey_Zhang
  • 2017年08月07日 16:05
  • 1045

sqlserver PIVOT和UNPIVOT使用

PIVOT和UNPIVOT使用详解
  • S630730701
  • S630730701
  • 2017年07月28日 11:14
  • 214

浅谈SQLServer行列转换UNPIVOT函数的使用

紧接着上一篇关于PIVOT函数的例子讲:点击打开 存在如下一张临时表: UNPIVOT函数: SELECT * FROM #Students unpivot(score for subject ...
  • Wikey_Zhang
  • Wikey_Zhang
  • 2017年08月07日 15:23
  • 416

SQL 2008行列转换的pivot--产生动态列

 突然间发现,已经好久没有写博客了,也好久没用SQLServer进行开发了。由于目前项目开发的原因,对于SQL Server才重视起来。发现SQL Server也引进了不少新的东西,现将一个不错...
  • langcai1981
  • langcai1981
  • 2014年01月21日 10:42
  • 7964

Oracle 11g行列互换pivot和unpivot说明

http://blog.163.com/magicc_love/blog/static/1858536622013981142431/ http://www.oracle-developer.net/...
  • u013071311
  • u013071311
  • 2015年06月25日 08:59
  • 1712

SQL中PIVOT 行转列和UNPIVOT列转行实例讲解

PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT与PIVOT执行相反的操作,将表值表达式的列转换为列值。 ...
  • u010541307
  • u010541307
  • 2016年10月22日 21:09
  • 4898

Sql 行列转换 动态Sql(Pivot)

Sql 行列转换在实际应用中是比较常用的,本文将列举几种行列转换的小例子 结果: StuName C# javascript Sql Serv...
  • Ranen2010
  • Ranen2010
  • 2011年04月22日 12:16
  • 8401
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server中行列转换 Pivot UnPivot
举报原因:
原因补充:

(最多只允许输入30个字)