成都网站开发外包,运维工程师累吗,专业做网站,大型网站seo课程SQL Server中行列转换 Pivot UnPivot PIVOT用于将列值旋转为列名#xff08;即行转列#xff09;#xff0c;在SQL Server 2000可以用聚合函数配合CASE语句实现PIVOT的一般语法是#xff1a;PIVOT(聚合函数(列) FOR 列 in (…) )AS P 完整语法#xff1a; table_source PI… 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、建立表格 createtabletb(姓名varchar(10),课程varchar(10),分数int) insertintotbvalues(张三,语文,74)insertintotbvalues(张三,数学,83)insertintotbvalues(张三,物理,93)insertintotbvalues(李四,语文,74)insertintotbvalues(李四,数学,84)insertintotbvalues(李四,物理,94) select*fromtb 姓名 课程 分数 ---------- ---------- ----------- 张三 语文 74 张三 数学 83 张三 物理 93 李四 语文 74 李四 数学 84 李四 物理 94 2、使用SQL Server 2000静态SQL 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,指课程不止语文、数学、物理这三门课程。(以下同) --变量按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 74 4、使用SQL Server 2005静态SQL select*fromtb pivot(max(分数)for课程in(语文,数学,物理))a 5、使用SQL Server 2005动态SQL --使用stuff() declaresqlvarchar(8000) setsql --初始化变量sql selectsqlsql,课程fromtbgroupby课程--变量多值赋值 setsqlstuff(sql,1,1,)--去掉首个, setsqlselect * from tb pivot (max(分数) for课程in (sql))a exec(sql) --或使用isnull() declaresqlvarchar(8000) –-获得课程集合 selectsqlisnull(sql,,)课程fromtbgroupby课程 setsqlselect * 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 declaresqlvarchar(500) setsqlselect姓名 selectsqlsql,max(case课程when 课程 then分数else 0 end)[课程] from(selectdistinct课程fromtb)a setsqlsql,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() -- declaresqlvarchar(8000) setsql --初始化变量sql selectsqlsql,课程fromtbgroupby课程--变量多值赋值 --同select sql sql ,课程from (select distinct课程from tb)a setsqlstuff(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姓名) n where 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姓名) 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。 --调用系统表动态生态。 declaresqlvarchar(8000) selectsqlisnull(sql union all ,) select姓名, [课程] quotename(Name,) , [分数] quotename(Name) from tb fromsyscolumns whereName!姓名andIDobject_id(tb)--表名tb不包含列名为姓名的其他列 orderbycolid exec(sql order by姓名) go 3、使用SQL Server 2005静态SQL --SQL SERVER 2005动态SQL select姓名,课程,分数fromtb unpivot (分数for课程in([语文],[数学],[物理])) t 4、使用SQL Server 2005动态SQL --SQL SERVER 2005动态SQL declaresqlnvarchar(4000) selectsqlisnull(sql,,)quotename(Name) fromsyscolumns whereIDobject_id(tb)andNamenotin(姓名) orderbyColid setsqlselect姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in(sql))b exec(sql) 该文章经过本人整理所得,欢迎转载,转载时请加上本文地址;本文基于署名 2.5 中国大陆许可协议发布请勿演绎或用于商业目的但是必须保留本文的署名张志涛包含链接如您有任何疑问或者授权方面的协商请给我留言。 转载于:https://www.cnblogs.com/edword/archive/2013/04/28/3048549.html
