关闭

SQL Server中行列转换 Pivot UnPivot

268人阅读 评论(0) 收藏 举报

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)

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:686882次
    • 积分:8182
    • 等级:
    • 排名:第2555名
    • 原创:134篇
    • 转载:255篇
    • 译文:1篇
    • 评论:86条
    最新评论