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)
420
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
