`

SQL SERVER 中行列转换 PIVOT UNPIVO

 
阅读更多
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 子句。
第二个阶段将把列表中的值扩展到他们相应的目标列上,
第三个阶段对每个表达式应用指定的聚合函数,生成结果列

典型实例

一、行转列

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 2008行列转换的pivot

    ### SQL 2008 行列转换 (Pivot) 的动态实现 #### 知识点一:行列转换(Pivot)的概念与应用场景 在数据库查询中,有时我们需要将数据表中的行转换为列,或者将列转换为行,这种操作被称为行列转换。行列转换在报表...

    SQL行列转换 Pivot UnPivot

    Pivot 和 UnPivot 是 SQL Server 2005 中引入的两个语法,用于实现行列转换。 Pivot Pivot 语法的主要作用是将列值旋转为列名,即行转列。其基本语法为: ``` SELECT * FROM table_source PIVOT (聚合函数(value_...

    sql server 行列转换

    通过上述示例可以看出,使用动态SQL可以有效地实现SQL Server中的行列转换。虽然这种方法具有一定的灵活性和适用性,但在实际应用中还需要考虑其潜在的安全性和维护性问题。在实际开发过程中,应根据具体需求和场景...

    SQL Server 2005新功能之PIVOT的描述

    在SQL Server 2005中,PIVOT是一个重要的新特性,它使得数据的行列转换更为便捷。在SQL Server 2000及其以前版本,如果需要将数据的某列值转换为行对应的列,通常需要使用复杂的CASE语句配合聚合函数(如SUM)来实现...

    SQLServer行列互转实现思路(聚合函数)

    在SQL Server中,行列互转是一种常见的数据处理需求,它能帮助我们以更直观的方式展示数据。本篇文章将深入探讨如何使用聚合函数Pivot和Unpivot来实现这一目标,特别是针对SQL Server数据库。 首先,让我们了解什么...

    sql查询中行列转换

    ### SQL查询中的行列转换 在数据库管理中,经常会遇到数据结构需要调整的情况,尤其是当原始数据的排列方式与实际需求不一致时。本篇文章将详细解释如何通过一条SQL查询语句实现行列转换,并且会针对两种不同的SQL ...

    sqlserver2005 行列转换实现方法

    Pivot是SQL Server中的一个功能,用于将行转换为列。在这个例子中,我们可以先通过JOIN操作将`Company`和`Product`连接,然后使用Pivot将产品数量按公司分组并转换为列。 以下是一个使用动态Pivot的例子,动态Pivot...

    深入探讨SQL Server 2008 R2 -PowerPivot

    在 SharePoint 场中,PowerPivot for SharePoint 添加了服务器端应用程序和功能,支持对您发布PowerPivot指的是一组应用程序和服务,它们为使用Excel和SharePoint来创建和共享商业智能提供了端到端的解决方案。...

    Oracle SQLServer行列转换

    ### Oracle SQLServer 行列转换知识点详解 #### 一、SQLServer 行转列方法 在SQLServer中,实现行转列的操作有两种主要方法:一种是利用`CASE...WHEN`语句配合`GROUP BY`聚合函数的方式;另一种是通过`PIVOT`操作...

    sql server通过pivot对数据进行行列转换的方法

    总结起来,SQL Server的Pivot功能提供了强大的数据行列转换能力,使得数据的展现更加直观和清晰。掌握这一技巧对于提升数据处理效率和质量具有重要意义。在处理类似的时间序列数据或者分类数据时,Pivot操作尤其有用...

    SQL SERVER 2005+ 版本行列转换数据脚本

    虽然题目中未明确提到行列转换的具体实现,但在实际应用场景中,如果需要对上述数据进行行列转换(例如,按月份汇总每个用户的得分),则可以使用SQL Server提供的`PIVOT`操作来实现。例如: ```sql SELECT * FROM ...

    SQL Server 关于Pivot详解

    在本文中,我们将详细介绍 SQL Server 中的 Pivot 语句,并结合实例分析和实验题目,帮助读者快速掌握其使用。 Pivot 语法 Pivot 语句的基本语法如下: ``` SELECT &lt;非透视的列&gt;, [第一个透视的列] AS &lt;列名称&gt;...

    Sql Server 2014 安装包

    - **Power Query和Power Pivot集成**: 在Excel中可以直接连接到SQL Server,使用Power Query进行数据发现和Power Pivot进行数据分析。 4. **安装过程**: - 首先,运行`SQLEXPR_x64_CHS.exe`安装SQL Server ...

    SQL语句行列转换(附带数据库、表、视图操作)

    SQL 语句行列转换是数据库管理系统中的一种常见操作,它可以将数据从行转换为列,或者从列转换为行。在这个过程中,需要使用数据库管理语言(Database Management Language,DML)来实现数据的转换。下面我们将详细...

    通过SQL语句实现行列转换的几种方法

    ### 通过SQL语句实现行列转换的几种方法 在日常工作中,我们经常需要处理的数据结构并不总是按照我们期望的方式组织的。特别是在制作自定义报表或进行产品开发时,经常会遇到需要将数据从一种布局转换到另一种布局...

    SQL2000 和 SQL2005 下 行列转换 示例 - freeliver54 - 博客园.pdf

    在SQL Server 2005中,可以通过PIVOT函数更加方便地实现行转列操作: ```sql SELECT StuName, [语文] AS '语文', [数学] AS '数学', [历史] AS '历史' FROM StuSources PIVOT ( SUM(ChengJi) FOR KeCheng IN ...

    在Sql Server 数据库中利用存储过程实现动态交叉表

    在SQL Server中,这通常需要使用到PIVOT(行列转换)和UNPIVOT(行列还原)操作来实现。 存储过程允许将创建动态交叉表的逻辑代码集中存储,并可以重复调用,提高了数据操作的灵活性和效率。实现动态交叉表的关键...

Global site tag (gtag.js) - Google Analytics