PIVOT用于将列值旋转为列名、同时执行聚合运算(即行转列),在SQL Server 2000可以用聚合函数配合CASE语句实现
PIVOT的一般语法是:PIVOT(聚合函数(列) FOR 列 in (…) )AS P
完整语法:
table_source
PIVOT(
聚合函数(value_column)
FOR pivot_column
IN(<column_list>)
)
PIVOT操作涉及三逻辑阶段
1,分组
2,扩展
3,聚合
第一阶段会隐匿地对进地分组,分组依据是那些未作为PIVOT输出的列,就像是有一个隐藏的GROUP BY 子句。
第二个阶段将把列表中的值扩展到他们相应的目标列上,
第三个阶段对每个表达式应用指定的聚合函数,生成结果列
PIVOT的一般语法是:PIVOT(聚合函数(列) FOR 列 in (…) )AS P
完整语法:
table_source
PIVOT(
聚合函数(value_column)
FOR pivot_column
IN(<column_list>)
)
PIVOT操作涉及三逻辑阶段
1,分组
2,扩展
3,聚合
第一阶段会隐匿地对进地分组,分组依据是那些未作为PIVOT输出的列,就像是有一个隐藏的GROUP BY 子句。
第二个阶段将把列表中的值扩展到他们相应的目标列上,
第三个阶段对每个表达式应用指定的聚合函数,生成结果列
典型实例 一、行转列 1、建立表格 ifobject_id('tb')isnotnulldroptabletb go createtabletb(姓名varchar(10),课程varchar(10),分数int) insert into tb values('张三','语文',74) insert into tb values('张三','数学',83) insert into tb values('张三','物理',93) insert into tb values('李四','语文',74) insert into tb values('李四','数学',84) insert into tb values('李四','物理',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)
发表评论
-
转:避免全表扫描的sql优化
2015-02-05 16:37 1322http://tech.diannaodian.com/dw/ ... -
给表加上聚集索引和非聚集索引
2014-12-11 17:28 700创建简单非聚集索引 以下示例为 Purchasing.Pro ... -
查看表中索引语句
2014-12-11 10:37 678SELECT object_name(object_id) ... -
SQL2008一行转多行的精典写法
2013-08-28 12:06 415--创建测试表 create table t1( id ... -
在sql server中利用with as实现递归功能
2013-06-25 10:48 532在sqlserver2005之前,要实现递归功能比较麻烦,比如 ... -
SQL 2005 with(nolock)详解
2013-06-17 17:03 583大家在写查询时, ... -
用SQL语句查询每门成绩都大于80的学生姓名
2013-05-29 10:39 1723昨天遇到的一个SQL面试题,感觉很有趣。 表名stu 结构 ... -
SqlServer强制断开数据库已有连接的方法
2013-02-22 11:03 1619在master数据库中执行如下代码 declare @i IN ... -
Sql获取第一天、最后一天
2013-01-03 14:56 9677① 本月第一天(--减去今天再加上1天) SELECT DAT ... -
表函数与游标
2012-12-25 16:07 743--建立数据源 create table tb1(zm ch ... -
sql server 2005 输出表的函數用法
2012-12-25 16:02 835view sourceprint? 01 --sql ser ... -
SQL杂谈
2012-12-14 17:33 7291、如何用convert什么的把带有时分秒的日期转为时分秒都0 ... -
用FOR XML PATH将查询结果以XML输出
2012-09-20 17:54 1039本文从此而来 http://www.cnblogs.com/d ... -
left join on and
2012-09-20 10:23 886数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临 ... -
筛选索引简单使用
2012-08-17 11:29 1154对于在强制实施数据完整性时的一种常见需求,也可以用筛选索引来解 ... -
排名函数(ROW_NUMBER、RANK、DENSE_RANK)及OVER子句
2012-08-10 10:36 6629--在部门内新水排名(如果有相同名次,用dense_rank) ... -
Apply简单使用举例
2012-08-09 17:20 1027APPLY APPLY运算符把右表表达式应用左输入中 ... -
SQL Server 2008语句大全完整版
2012-08-09 10:39 1391--======================== ... -
Not Exists练习
2012-07-23 14:40 888IF OBJECT_ID(N'a') IS NOT NU ... -
Exists练习查找最小缺失值(Missing Value)
2012-07-17 09:34 997第一种方法 USE tempdb; GO IF OBJE ...
相关推荐
### SQL 2008 行列转换 (Pivot) 的动态实现 #### 知识点一:行列转换(Pivot)的概念与应用场景 在数据库查询中,有时我们需要将数据表中的行转换为列,或者将列转换为行,这种操作被称为行列转换。行列转换在报表...
Pivot 和 UnPivot 是 SQL Server 2005 中引入的两个语法,用于实现行列转换。 Pivot Pivot 语法的主要作用是将列值旋转为列名,即行转列。其基本语法为: ``` SELECT * FROM table_source PIVOT (聚合函数(value_...
通过上述示例可以看出,使用动态SQL可以有效地实现SQL Server中的行列转换。虽然这种方法具有一定的灵活性和适用性,但在实际应用中还需要考虑其潜在的安全性和维护性问题。在实际开发过程中,应根据具体需求和场景...
在SQL Server 2005中,PIVOT是一个重要的新特性,它使得数据的行列转换更为便捷。在SQL Server 2000及其以前版本,如果需要将数据的某列值转换为行对应的列,通常需要使用复杂的CASE语句配合聚合函数(如SUM)来实现...
在SQL Server中,行列互转是一种常见的数据处理需求,它能帮助我们以更直观的方式展示数据。本篇文章将深入探讨如何使用聚合函数Pivot和Unpivot来实现这一目标,特别是针对SQL Server数据库。 首先,让我们了解什么...
### SQL查询中的行列转换 在数据库管理中,经常会遇到数据结构需要调整的情况,尤其是当原始数据的排列方式与实际需求不一致时。本篇文章将详细解释如何通过一条SQL查询语句实现行列转换,并且会针对两种不同的SQL ...
在 SharePoint 场中,PowerPivot for SharePoint 添加了服务器端应用程序和功能,支持对您发布PowerPivot指的是一组应用程序和服务,它们为使用Excel和SharePoint来创建和共享商业智能提供了端到端的解决方案。...
Pivot是SQL Server中的一个功能,用于将行转换为列。在这个例子中,我们可以先通过JOIN操作将`Company`和`Product`连接,然后使用Pivot将产品数量按公司分组并转换为列。 以下是一个使用动态Pivot的例子,动态Pivot...
### Oracle SQLServer 行列转换知识点详解 #### 一、SQLServer 行转列方法 在SQLServer中,实现行转列的操作有两种主要方法:一种是利用`CASE...WHEN`语句配合`GROUP BY`聚合函数的方式;另一种是通过`PIVOT`操作...
总结起来,SQL Server的Pivot功能提供了强大的数据行列转换能力,使得数据的展现更加直观和清晰。掌握这一技巧对于提升数据处理效率和质量具有重要意义。在处理类似的时间序列数据或者分类数据时,Pivot操作尤其有用...
虽然题目中未明确提到行列转换的具体实现,但在实际应用场景中,如果需要对上述数据进行行列转换(例如,按月份汇总每个用户的得分),则可以使用SQL Server提供的`PIVOT`操作来实现。例如: ```sql SELECT * FROM ...
在本文中,我们将详细介绍 SQL Server 中的 Pivot 语句,并结合实例分析和实验题目,帮助读者快速掌握其使用。 Pivot 语法 Pivot 语句的基本语法如下: ``` SELECT <非透视的列>, [第一个透视的列] AS <列名称>...
- **Power Query和Power Pivot集成**: 在Excel中可以直接连接到SQL Server,使用Power Query进行数据发现和Power Pivot进行数据分析。 4. **安装过程**: - 首先,运行`SQLEXPR_x64_CHS.exe`安装SQL Server ...
SQL 语句行列转换是数据库管理系统中的一种常见操作,它可以将数据从行转换为列,或者从列转换为行。在这个过程中,需要使用数据库管理语言(Database Management Language,DML)来实现数据的转换。下面我们将详细...
### 通过SQL语句实现行列转换的几种方法 在日常工作中,我们经常需要处理的数据结构并不总是按照我们期望的方式组织的。特别是在制作自定义报表或进行产品开发时,经常会遇到需要将数据从一种布局转换到另一种布局...
在SQL Server 2005中,可以通过PIVOT函数更加方便地实现行转列操作: ```sql SELECT StuName, [语文] AS '语文', [数学] AS '数学', [历史] AS '历史' FROM StuSources PIVOT ( SUM(ChengJi) FOR KeCheng IN ...
在SQL Server中,这通常需要使用到PIVOT(行列转换)和UNPIVOT(行列还原)操作来实现。 存储过程允许将创建动态交叉表的逻辑代码集中存储,并可以重复调用,提高了数据操作的灵活性和效率。实现动态交叉表的关键...