SQLServer中⾏列转置⽅法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、建⽴表格
ifobject_id('tb')isnotnulldroptabletb
go
createtabletb(姓名varchar(10)，课程varchar(10)，分数int)
insertintotbvalues('张三','语⽂',74)
insertintotbvalues('张三','数学',83)
insertintotbvalues('张三','物理',93)
insertintotbvalues('李四','语⽂',74)
insertintotbvalues('李四','数学',84)
insertintotbvalues('李四','物理',94)
go
select*fromtb
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)物理
fromtb
groupby姓名
姓名语⽂数学物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
3、使⽤SQL Server 2000动态SQL
-
-SQL SERVER 2000动态SQL，指课程不⽌语⽂、数学、物理这三门课程。(以下同)
select中distinct--变量按sql语⾔顺序赋值
declare@sqlvarchar(500)
set@sql='select姓名'
select@sql=@sql+',max(case课程when '''+课程+''' then分数else 0 end)['+课程+']'
from(selectdistinct课程fromtb)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(selectdistinct课程fromtb)asa
set@sql='select姓名,'+@sql+' from tb group by姓名'
exec(@sql)
姓名数学物理语⽂
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
4、使⽤SQL Server 2005静态SQL
select*fromtb pivot(max(分数)for课程in(语⽂，数学，物理))a
5、使⽤SQL Server 2005动态SQL
--使⽤stuff()
declare@sqlvarchar(8000)
set@sql='' --初始化变量@sql
select@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值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+',','')+课程fromtbgroupby课程
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)数学,
max(case课程when'物理'then分数else0end)物理,
sum(分数)总分,
cast(avg(分数*1.0)asdecimal(18,2))平均分
fromtb
groupby姓名
姓名语⽂数学物理总分平均分
---------- ----------- ----------- ----------- -----------
李四 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(selectdistinct课程fromtb)a
set@sql=@sql+',sum(分数)总分,cast(avg(分数*1.0) as decimal(18,2)) 平均分from tb group by姓名' exec(@sql)
3、使⽤SQL Server 2005静态SQL
selectm.*,n.总分,n.平均分
from
(select*fromtb pivot(max(分数)for课程in(语⽂，数学，物理))a)m,
(select姓名,sum(分数)总分,cast(avg(分数*1.0)asdecimal(18,2))平均分
fromtb
groupby姓名)n
wherem.姓名=n.姓名
4、使⽤SQL Server 2005动态SQL
--使⽤stuff()
--
declare@sqlvarchar(8000)
set@sql='' --初始化变量@sql
select@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值
-
-同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+',','')+课程fromtbgroupby课程
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、建⽴表格
ifobject_id('tb')isnotnulldroptabletb
go
createtabletb(姓名varchar(10)，语⽂int，数学int，物理int) insertintotbvalues('张三',74,83,93)
insertintotbvalues('李四',74,84,94)
go
select*fromtb
go
姓名语⽂数学物理
---------- ----------- ----------- -----------
张三 74 83 93
李四 74 84 94
2、使⽤SQL Server 2000静态SQL
--SQL SERVER 2000静态SQL。
select*from
(
select姓名，课程='语⽂'，分数=语⽂fromtb
unionall
select姓名，课程='数学'，分数=数学fromtb
unionall
select姓名，课程='物理'，分数=物理fromtb
) t
orderby姓名,case课程when'语⽂'then1when'数学'then2when'物理'then3end 姓名课程分数
---------- ---- -----------
李四语⽂ 74
李四数学 84
李四物理 94
张三语⽂ 74
张三数学 83
张三物理 93
2、使⽤SQL Server 2000动态SQL
--SQL SERVER 2000动态SQL。
--调⽤系统表动态⽣态。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。