SQLServer中行列转换PivotUnPivot.doc

SQL Server中行列转换 Pivot UnPivotPIVOT用于将列值旋转为列名（即行转列），在SQLServer2000可以用聚合函数配合CASE语句实现PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in () )AS P完整语法：table_sourcePIVOT(聚合函数（value_column）FOR pivot_columnIN()UNPIVOT用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现完整语法：table_sourceUNPIVOT(value_columnFOR pivot_columnIN()注意：PIVOT、UNPIVOT是SQL Server2005的语法，使用需修改数据库兼容级别在数据库属性-选项-兼容级别改为 90典型实例一、行转列1、建立表格ifobject_id(tb)isnotnulldroptabletbgocreatetabletb(姓名varchar(10),课程varchar(10),分数int)insertintotbvalues(张三,语文,74)insertintotbvalues(张三,数学,83)insertintotbvalues(张三,物理,93)insertintotbvalues(李四,语文,74)insertintotbvalues(李四,数学,84)insertintotbvalues(李四,物理,94)goselect*fromtbgo姓名课程分数- - -张三语文 74张三数学 83张三物理 93李四语文 74李四数学 84李四物理 942、使用SQL Server 2000静态SQL-cselect姓名,max(case课程when语文then分数else0end)语文,max(case课程when数学then分数else0end)数学,max(case课程when物理then分数else0end)物理fromtbgroupby姓名姓名语文数学物理- - - -李四 74 84 94张三 74 83 933、使用SQL Server 2000动态SQL-SQL SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)-变量按sql语言顺序赋值declaresqlvarchar(500)setsql=select姓名selectsql=sql+,max(case课程when +课程+then分数else 0 end)+课程+from(selectdistinct课程fromtb)a-同from tb group by课程，默认按课程名排序setsql=sql+ from tb group by姓名exec(sql)-使用isnull(),变量先确定动态部分declaresqlvarchar(8000)selectsql=isnull(sql+,)+ max(case课程when +课程+ then分数else 0 end) +课程+from(selectdistinct课程fromtb)asasetsql=select姓名,+sql+ from tb group by姓名exec(sql)姓名数学物理语文- - - -李四 84 94 74张三 83 93 744、使用SQL Server 2005静态SQLselect*fromtbpivot(max(分数)for课程in(语文,数学,物理)a5、使用SQL Server 2005动态SQL-使用stuff()declaresqlvarchar(8000)setsql=-初始化变量sqlselectsql=sql+,+课程fromtbgroupby课程-变量多值赋值setsql=stuff(sql,1,1,)-去掉首个,setsql=select * from tbpivot (max(分数) for课程in (+sql+)aexec(sql)-或使用isnull()declaresqlvarchar(8000)-获得课程集合selectsql=isnull(sql+,)+课程fromtbgroupby课程setsql=select * from tbpivot (max(分数) for课程in (+sql+)aexec(sql)二、行转列结果加上总分、平均分1、使用SQL Server 2000静态SQL-SQL SERVER 2000静态SQLselect姓名,max(case课程when语文then分数else0end)语文,max(case课程when数学then分数else0end)数学,max(case课程when物理then分数else0end)物理,sum(分数)总分,cast(avg(分数*1.0)asdecimal(18,2)平均分fromtbgroupby姓名姓名语文数学物理总分平均分- - - - -李四 74 84 94 252 84.00张三 74 83 93 250 83.332、使用SQL Server 2000动态SQL-SQL SERVER 2000动态SQLdeclaresqlvarchar(500)setsql=select姓名selectsql=sql+,max(case课程when +课程+then分数else 0 end)+课程+from(selectdistinct课程fromtb)asetsql=sql+,sum(分数)总分,cast(avg(分数*1.0) as decimal(18,2) 平均分from tb group by姓名exec(sql)3、使用SQL Server 2005静态SQLselectm.*,n.总分,n.平均分from(select*fromtbpivot(max(分数)for课程in(语文,数学,物理)a)m,(select姓名,sum(分数)总分,cast(avg(分数*1.0)asdecimal(18,2)平均分fromtbgroupby姓名)nwherem.姓名=n.姓名4、使用SQL Server 2005动态SQL-使用stuff()-declaresqlvarchar(8000)setsql=-初始化变量sqlselectsql=sql+,+课程fromtbgroupby课程-变量多值赋值-同select sql = sql + ,+课程from (select distinct课程from tb)asetsql=stuff(sql,1,1,)-去掉首个,setsql=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姓名) nwhere m.姓名= n.姓名exec(sql)-或使用isnull()declaresqlvarchar(8000)selectsql=isnull(sql+,)+课程fromtbgroupby课程setsql=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姓名) nwhere m.姓名= n.姓名exec(sql)二、列转行1、建立表格ifobject_id(tb)isnotnulldroptabletbgocreatetabletb(姓名varchar(10),语文int,数学int,物理int)insertintotbvalues(张三,74,83,93)insertintotbvalues(李四,74,84,94)goselect*fromtbgo姓名语文数学物理- - - -张三74 83 93李四 74 84 942、使用SQL Server 2000静态SQL-SQL SERVER 2000静态SQL。select*from(select姓名,课程=语文,分数=语文fromtbunionallselect姓名,课程=数学,分数=数学fromtbunionallselect姓名,课程=物理,分数=物理fromtb) torderby姓名,case课程when语文then1when数学then2when物理then3end姓名课程分数- - -李四语文74李四数学84李四物理94张三语文74张三数学83张三物理932、使用SQL Server 2000动态SQL-SQL SERVER 2000动态SQL。-调用系统表动态生态。declaresqlvarchar(8000)selectsql=isnull(sql+ union all ,)+ select姓名, 课程=+quotename(Name,)+ , 分数 = +quotename(Name)+ from tbfromsyscolumnswhereName!=姓名andID=object_id(tb)-表名tb，不包含列名为姓名的其他列orderbycolidexec(sql+ order by姓名)go3、使用SQL Server 2005静态SQL-SQL SERVER 2005动态SQLselect姓名,课程,分数fromtb unpivot (分数for课程in(语文,数学,物理) t4、使用SQL Server 2005动态SQL