SQL Server中行列转换Pivot UnPivot
PIVOT用于将列值旋转为列名（即行转列），在SQL Server 2000可以用聚合函数配合CASE 语句实现
PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P
完整语法：
table_source
PIVOT(
聚合函数（value_column）
FOR pivot_column
IN(
)
UNPIVOT用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现
完整语法：
table_source
UNPIVOT(
value_column
FOR pivot_column
IN(
)
注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需修改数据库兼容级别在数据库属性->选项->兼容级别改为 90
典型实例
一、行转列
1、建立表格
if object_id('tb')isnotnull droptable tb
go
createtable tb(姓名varchar(10),课程varchar(10),分数int) insertinto tb values('张三','语文',74)
insertinto tb values('张三','数学',83)
insertinto tb values('张三','物理',93)
insertinto tb values('李四','语文',74)
insertinto tb values('李四','数学',84)
insertinto tb values('李四','物理',94)
go
select*from tb
go
姓名课程分数
---------- ---------- -----------
张三语文 74
张三数学 83
张三物理 93
李四语文 74
李四数学 84
李四物理 94
2、使用SQL Server 2000静态SQL
--c
select姓名,
max(case课程when'语文'then分数else0end)语文,
max(case课程when'数学'then分数else0end)数学,
max(case课程when'物理'then分数else0end)物理
from tb
groupby姓名
姓名语文数学物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
3、使用SQL Server 2000动态SQL
--SQL SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。(以下同) --变量按sql语言顺序赋值
declare@sql varchar(500)
set@sql='select姓名'
select@sql=@sql+',max(case课程when '''+课程+''' then分数else 0 end)['+课程+']'
from(selectdistinct课程from tb)a--同from tb group by课程，默认按课程名排序set@sql=@sql+' from tb group by姓名'
exec(@sql)
--使用isnull(),变量先确定动态部分
declare@sql varchar(8000)
select@sql=isnull(@sql+',','')+' max(case课程when '''+课程+''' then分数else 0 end) ['+课程+']'
from(selectdistinct课程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@sql varchar(8000)
set@sql=''--初始化变量@sql
select@sql=@sql+','+课程from tb groupby课程--变量多值赋值
set@sql=stuff(@sql,1,1,'')--去掉首个','
set@sql='select * from tb pivot (max(分数) for课程in ('+@sql+'))a' exec(@sql)
--或使用isnull()
declare@sql varchar(8000)
–-获得课程集合
select@sql=isnull(@sql+',','')+课程from tb groupby课程
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分数else0end)语文,
max(case课程when'数学'then分数else0end)数学,
