Sql 语句收集——行转列

时间:2021-08-24 22:43:48
SQL行转列汇总

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,指课程不止语文、数学、物理这三门课程。(以下同) --变量按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。 --调用系统表动态生态。 declare@sqlvarchar(8000) select@sql=isnull(@sql+' union all ','')+' select姓名, [课程]=' +quotename(Name,'''')+' , [分数] = '+quotename(Name)+' from tb' fromsyscolumns whereName!='姓名'andID=object_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 declare@sqlnvarchar(4000) select@sql=isnull(@sql+',','')+quotename(Name) fromsyscolumns whereID=object_id('tb')andNamenotin('姓名') orderbyColid set@sql='select姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in('+@sql+'))b' exec(@sql)

Sql 语句收集——行转列的更多相关文章

  1. sql语句实现行转列的3种方法实例

    sql语句实现行转列的3种方法实例 一般在做数据统计的时候会用到行转列,假如要统计学生的成绩,数据库里查询出来的会是这样的,但这并不能达到想要的效果,所以要在查询的时候做一下处理,下面话不多说了,来一 ...

  2. mysql sql语句:行转列问题

    存在表score,记录学生的考试成绩,如下图所示: 现要求以 学生姓名,语文,数学,英语 这种格式显示学生成绩,如下图所示 具体步骤如下: 1.首先,使用case when函数输出单个课程的成绩 ca ...

  3. SQL语句实现行转列

    最近在维护一个项目,出现了一下bug需要进行调试,于是把正式库上面的代码搬到本地库上面,数据库是本地的,跑项目的时候调试发现代码里面带有wmsys.wm_concat函数的SQL语句出现错误,经排查发 ...

  4. sql语句实现行转列练习

    create table STUDENT_SCORE( name VARCHAR2(20), subject VARCHAR2(20), score NUMBER(4,1));insert into ...

  5. SQL 语句实现行转列

    CREATE TABLE #tempcloum( [productNum] varchar() null, [year1] decimal not null, [year2] decimal not ...

  6. SQL server 动态行转列

    用聚合函数配合CASE语句实现行转列功能: 现在分享一下具体实现代码: 转换前效果: PlanName PlanType PlanLimit 计划1 计划类型1 RMB 1,000,000 计划1 计 ...

  7. SQL语句之行操作

    SQL语句系列 1.SQL语句之行操作 2.SQL语句之表操作 3.SQL语句之数据库操作 4.SQL语句之用户管理 关系型数据库的存储形式 在关系型数据库中,数据都是以类似于Excel表格的形式存储 ...

  8. MySQL之唯一索引、外键的变种、SQL语句数据行操作补充

    0.唯一索引 unique对num进行唯一限制,表示num是独一无二的,uql是唯一索引名称 上面为联合索引:num和xx不能完全一样  1.外键的变种 a. 用户表和部门表 用户: 1 alex 1 ...

  9. sql中的行转列和列转行的问题

    sql中的行转列和列转行的问题 这是一个常见的问题,也是一个考的问题 1.行转列的问题  简单实例 CREATE TABLE #T ( MON1 INT, MON2 INT, MON3 INT ) G ...

随机推荐

  1. CI Weekly &num;5 &vert; 微服务架构下的持续部署与交付

    CI Weekly 围绕『 软件工程效率提升』 进行一系列技术内容分享,包括国内外持续集成.持续交付,持续部署.自动化测试. DevOps 等实践教程.工具与资源,以及一些工程师文化相关的程序员 Ti ...

  2. 使用XStream解析MXL文件用到的jar包---xpp3&lowbar;min-1&period;1&period;3&period;4&period;O&period;jar和xstream-1&period;3&period;1&period;jar

    使用XStream解析MXL文件用到的jar包---xpp3_min-1.1.3.4.O.jar和xstream-1.3.1.jar

  3. linux集群运维工具&colon;pssh

    由于需要安装hadoop集群,有10台机器需要安装,一开始打算用SCP复制,后来觉得不可接受(实际现场可能数倍的机器集群,就是10台也不想干).后来在网上找了,发现了clustershell和pssh ...

  4. PIG的配置

    Pig是一个客户端应用程序,就算你要在Hadoop集群上运行Pig,也不需要在集群上装额外的东西.Pig的配置非常简单: 1.下载pig,网址http://pig.apache.org/ 2.在机器上 ...

  5. ExtJS初接触 —— 了解 Ext Core

    ExtJS初接触 —— 了解 Ext Core Ext Core是一款和jQuery媲美的轻型JS库,基于MIT许可.对于Dom的操作,我个人还是比较喜欢用jQuery.当然如果项目中用的是ExtJS ...

  6. Asp&period;Net Core Use MongoDB

    前几天在网上想找一下Asp.Net Core使用MongoDB相关的文章,也看了几篇,发现都是在写简单的例子,这样的例子是不能用在生产环境的,不能用的生产环境的.封装一个东西一定是建立在你对这个东西足 ...

  7. 常用工具类(System&comma;Runtime&comma;Date&comma;Calendar&comma;Math)

    一.System: 一个java.lang包中的静态工具类. 三大字段: static PrintStream err “标准”错误输出流. static InputStream in “标准”输入流 ...

  8. Python &lowbar;Mix&ast;9

    1. 函数 函数是对功能的封装 语法: def 函数名(形参列表): 函数体(代码块) 代码块中有可能包含return 调用: 函数名(实参列表) def mix(a,b): #def 函数名(a和b ...

  9. LeetCode手记-Add Binary

    问题描述 问题分析 分析题意,此题实际是求解两个二进制数的和,但是有两点要注意: 1.字符串的长度不限,所以相应十进制数值很可能会超过int的上限. 2.二进制的加法规则是自右向左进位,需要注意,以题 ...

  10. Salesforce小知识:在简档中设置Visualforce页面的权限

    简档(Profile)中的 Visualforce 页面访问权限 在Salesforce中,对于自定义的简档,可以设置"Visualforce 页面访问"的权限. Visualfo ...