上海博大园林建设发展有限公司网站,网站改版降权,无锡企业网银,网站建设如何选择PIVOT用于将列值旋转为列名#xff08;即行转列#xff09;#xff0c;在SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是#xff1a;PIVOT(聚合函数(列) FOR 列 in (…) )AS P 完整语法#xff1a; table_source PIVOT( 聚合函数#xff08;value_col…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)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李四 物理 94 2、使用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)setsqlselect姓名selectsqlsql,max(case课程when 课程 then分数else 0 end)[课程]from(selectdistinct课程fromtb)a--同from tb group by课程默认按课程名排序setsqlsql from tb group by姓名exec(sql)--使用isnull(),变量先确定动态部分declaresqlvarchar(8000)selectsqlisnull(sql,,) max(case课程when 课程 then分数else 0 end) [课程]from(selectdistinct课程fromtb)asa setsqlselect姓名,sql from tb group by姓名exec(sql)姓名 数学 物理 语文---------- ----------- ----------- -----------李四 84 94 74张三 83 93 744、使用SQL Server 2005静态SQLselect*fromtb pivot(max(分数)for课程in(语文,数学,物理))a5、使用SQL Server 2005动态SQL--使用stuff()declaresqlvarchar(8000)setsql --初始化变量sqlselectsqlsql,课程fromtbgroupby课程--变量多值赋值setsqlstuff(sql,1,1,)--去掉首个,setsqlselect * from tb pivot (max(分数) for课程in (sql))aexec(sql)--或使用isnull()declaresqlvarchar(8000)–-获得课程集合selectsqlisnull(sql,,)课程fromtbgroupby课程 setsqlselect * from tb pivot (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)setsqlselect姓名selectsqlsql,max(case课程when 课程 then分数else 0 end)[课程]from(selectdistinct课程fromtb)asetsqlsql,sum(分数)总分,cast(avg(分数*1.0) as decimal(18,2)) 平均分from tb group by姓名exec(sql)3、使用SQL Server 2005静态SQLselectm.*,n.总分,n.平均分from(select*fromtb pivot(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 --初始化变量sqlselectsqlsql,课程fromtbgroupby课程--变量多值赋值--同select sql sql ,课程from (select distinct课程from tb)asetsqlstuff(sql,1,1,)--去掉首个,setsqlselect 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)selectsqlisnull(sql,,)课程fromtbgroupby课程setsqlselect 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姓名,课程语文,分数语文fromtb unionall select姓名,课程数学,分数数学fromtb unionall select姓名,课程物理,分数物理fromtb) torderby姓名,case课程when语文then1when数学then2when物理then3end姓名 课程 分数---------- ---- -----------李四 语文 74李四 数学 84李四 物理 94张三 语文 74张三 数学 83张三 物理 932、使用SQL Server 2000动态SQL--SQL SERVER 2000动态SQL。--调用系统表动态生态。declaresqlvarchar(8000)selectsqlisnull(sql union all ,) select姓名, [课程]quotename(Name,) , [分数] quotename(Name) from tbfromsyscolumnswhereName!姓名andIDobject_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--SQL SERVER 2005动态SQLdeclaresqlnvarchar(4000)selectsqlisnull(sql,,)quotename(Name)fromsyscolumnswhereIDobject_id(tb)andNamenotin(姓名)orderbyColidsetsqlselect姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in(sql))bexec(sql) 转载于:https://www.cnblogs.com/tangqs/archive/2012/02/11/2347146.html
