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中行列转换 Pivot UnPivot

转自:http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html PIVOT用于将列值旋转为列名(即行转列),在SQL Serv...

SQL Server 2005 中行列转换(Pivot 和 UNPivot的使用)

针对sql2005  系统提供两个新的关键字 PIVOT 和UNPIVOT可用来作此类操作. 语法规则 ::=         ( aggregate_function ( value_column...

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

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

SQL Server中行列转换 Pivot UnPivot

(转自cnblog张志涛)    PIVOT用于将列值旋转为列名(即行转列),在SQL Server2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是:PIVOT(聚合函...
  • kamboo
  • kamboo
  • 2011年09月19日 17:22
  • 234

SQL Server 2005之PIVOT/UNPIVOT行列转换

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

sql server 行列转换PIVOT 和 UNPIVOT

PIVOT用于将列值旋转为列名(即行转列),在SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是:PIVOT(聚合函数(列) FOR 列 in (…...

SQL Server 行列转换 Pivot UnPivot

PIVOT用于将列值旋转为列名(即行转列),在SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是:PIVOT(聚合函数(列) FOR 列 in (…) ...

使用 PIVOT 和 UNPIVOT(MS SQL Server行列转换)

可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执...
  • Baple
  • Baple
  • 2011年06月29日 11:47
  • 843

Sql Server函数大全(二)---SQL Server 2005 中行列转换(Pivot 和 UNPivot的使用)

Sql Server函数大全(二)---SQL Server 2005 中行列转换(Pivot 和 UNPivot的使用)
  • cpcpc
  • cpcpc
  • 2011年02月28日 11:19
  • 988

SQL行列转换 Pivot UnPivot

  • 2011年05月27日 12:37
  • 38KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server中行列转换 Pivot UnPivot
举报原因:
原因补充:

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