SQL Server中行列转换 Pivot UnPivot

最新推荐文章于 2022-08-02 19:05:06 发布
转载 最新推荐文章于 2022-08-02 19:05:06 发布 · 936 阅读 · 0
收录于
SQL

当前文章被收录于：

SQL
23 篇文章 2 人学习
订阅专栏 查看详情
本文介绍SQLServer中的行列转换方法，包括使用PIVOT和UNPIVOT进行数据重塑的技术细节。涵盖从SQLServer 2000到2005的不同版本特性及动态SQL的应用。

SQL Server中行列转换 Pivot UnPivot

PIVOT用于将列值旋转为列名（即行转列），在SQL Server2000可以用聚合函数配合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)


--PIVOT/UNPIVOT的用法(行列转换)
 
　　--PIVOT的用法：
 
　　--首先创建测试表，然后插入测试数据
 
　　create table test(id int,name varchar(20),quarter int,profile int) 
 
　　insert into test values(1,'a',1,1000)
 
　　insert into test values(1,'a',2,2000)
 
　　insert into test values(1,'a',3,4000)
 
　　insert into test values(1,'a',4,5000)
 
　　insert into test values(2,'b',1,3000)
 
　　insert into test values(2,'b',2,3500)
 
　　insert into test values(2,'b',3,4200)
 
　　insert into test values(2,'b',4,5500)
 
　　select * from test
 
　　--id name quarter profile
 
　　----------- -------------- ----------- -----------
 
　　--1 a 1 1000
 
　　--1 a 2 2000
 
　　--1 a 3 4000
 
　　--1 a 4 5000
 
　　--2 b 1 3000
 
　　--2 b 2 3500
 
　　--2 b 3 4200
 
　　--2 b 4 5500
 
　　--(8 row(s) affected)
 
　　--使用PIVOT将四个季度的利润转换成横向显示:
 
　　select id,name,
 
　　[1] as "一季度",
 
　　[2] as "二季度",
 
　　[3] as "三季度",
 
　　[4] as "四季度"
 
　　from
 
　　test
 
　　pivot
 
　　(
 
　　sum(profile)
 
　　for quarter in
 
　　([1],[2],[3],[4])
 
　　)
 
　　as pvt
 
　　--id name 一季度 二季度 三季度 四季度
 
　　-------- --------- ----------- -------- ------- -------
 
　　--1 a 1000 2000 4000 5000
 
　　--2 b 3000 3500 4200 5500
 
　　--(2 row(s) affected)
 
　　--UNPIVOT的用法：
 
　　--首先建立测试表，然后插入测试数据
 
　　drop table test
 
　　create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
 
　　insert into test values(1,'a',1000,2000,4000,5000)
 
　　insert into test values(2,'b',3000,3500,4200,5500)
 
　　select * from test
 
　　--id name Q1 Q2 Q3 Q4
 
　　-------- ------- --------- --------- -------- --------
 
　  --1 a 1000 2000 4000 5000
 
　　--2 b 3000 3500 4200 5500
 
　　--(2 row(s) affected)
 
　　--使用UNPIVOT，将同一行中四个季度的列数据转换成四行数据：
 
　　select id,name,quarter,profile
 
　　from
 
　　test
 
　　unpivot
 
　　(
 
　　profile
 
　　for quarter in
 
　　([Q1],[Q2],[Q3],[Q4])
 
　　) 
 
　　as unpvt
 
　　--id name quarter profile
 
　　----------- ----------- ---------- -----------
 
　　--1 a Q1 1000
 
　　--1 a Q2 2000
 
　　--1 a Q3 4000
 
　　--1 a Q4 5000
 
　　--2 b Q1 3000
 
　　--2 b Q2 3500
 
　　--2 b Q3 4200
 
　　--2 b Q4 5500
 
　　--(8 row(s) affected)

DeepSeek Harness 桌面端 把复杂的 Agent 调度变成开箱即用的软件，为 DeepSeek Harness (DSH) 插件生态打造的现代化桌面端解决方案
08-22
DeepSeek Harness 桌面端 把复杂的 Agent 调度变成开箱即用的软件，为 DeepSeek Harness (DSH) 插件生态打造的现代化桌面端解决方案。 DeepSeek Harness Desktop 将原本依赖命令行启动的本地 Web UI 封装成了原生桌面应用，支持 macOS 和 Windows。它解决了用户需要手动配置 Node.js 环境的门槛，集成了插件市场、移动端远程控制以及微信、飞书等社交软件的接入能力。 该项目通过桌面窗口和系统托盘管理本地服务的生命周期，让模型适配器、工具注册表等复杂逻辑实现了开箱即用。目前大模型生态正从简单的对话框转向复杂的任务流，但本地运行 Agent 的配置成本阻碍了大多数人。 这个项目的核心价值在于其基于 Cordis 构建的插件化架构，它试图建立一个 AI 时代的工具箱，让用户能像安装浏览器插件一样组合 AI 能力。社区评论普遍认为这种封装极大降低了探索 DeepSeek 生态的成本，让普通人也能触碰 Agent 调度
Python PDF流压缩 体积对比报告
最新发布
08-22
Python PDF流压缩 体积对比报告 对 PDF 做流压缩重写，缺省自动生成演示 PDF，输出 compressed.pdf、compress_report.csv 与体积对比图。 功能： · PDF 流压缩重写 · 缺省自动生成演示 PDF · compressed.pdf 输出 · compress_report.csv · 体积对比条形图 · 打包时预跑 output/preview 压缩包含可运行源码、依赖与说明，按 README 安装后即可复现。
Sqlserver行转列pivot()
热门推荐
miaomiaotiaopi的博客
08-02 1万+
sqlserver行转列
psd模板-006-光环翅膀
08-22
psd模板-006-光环翅膀
SQL SERVER 行列转换（2）——PIVOT、UNPIVOT 转换
二月十六的博客
04-09 6928
       上篇博客介绍了静态方式的行列转换，这次介绍一下利用SQL SERVER 函数实现的行列转换，行转列测试数据同之前的一样：--测试数据 if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([Name] nvarchar(22),[Subject] nvarchar(22...
sqlserver行与列的数据互转（pivot、unpivot）
weixin_46596213的博客
10-17 1753
用到的数据库和表： 用到的数据库是ERP_DB 用到的表是Account，Id是主键自增，EmpNo是员工编号，Money是销售额，RecordDate 记录时间。 表中的数据：（目前表中只有2和3两个员工） 实现效果 ：每个月的员工的销售总额！（要求行转列显示） 思考：如何获取RecordDate中的月份？ 答：用DATENAME函数 select DATENAME(MONTH,RecordDate) from Account a 一、行转列 pivot 首先创建一个临时.
sql 行转列
小楼听风雨
03-14 293
SQL Server中行列转换 Pivot UnPivot PIVOT用于将列值旋转为列名（即行转列），在SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P 完整语法： table_source PIVOT( 聚合函数（value_column） FOR ...
isnull pivot server sql_sql server 行列转换PIVOT 和 UNPIVOT | 学步园
weixin_39805883的博客
12-20 269
PIVOT用于将列值旋转为列名(即行转列)，在SQLServer2000可以用聚合函数配合CASE语句实现PIVOT的一般语法是：PIVOT(聚合函数(列)FOR 列 in (…) )AS P完整语法：table_sourcePIVOT(聚合函数(value_column)FOR pivot_columnIN())UNPIVOT用于将列明转为列值(即列转行)，在SQLServer 2000可以用...
isnull pivot server sql_SQL Server中行列转换 Pivot UnPivot
weixin_39692045的博客
12-20 179
原标题：SQL Server中行列转换 Pivot UnPivotPIVOT用于将列值旋转为列名(即行转列)，在SQL Server 2000可以用聚合函数配合CASE语句实现 请点击此处输入图片描述PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P完整语法：table_sourcePIVOT(聚合函数(value_column)FOR pivot_colum...
SQL Server 2005之PIVOT/UNPIVOT行列转换
congzhan1377的博客
06-20 58
SQL Server2005引入了很多迎合开发者口味的新特性，虽然改动不大，却大大了减少了开发者的工作量，这种替用户考虑的开发思路，值得称赞。在SQL Server2000中，要实现行列转换，需要综合利用聚合函数和动态SQL，实...
PIVOT/UNPIVOT SQL Server 2005 行列转换
04-30 2310
1.=================================================http://untitled.spaces.live.com/blog/cns!86b82838704e0d5a!783.entry微软SQL Server 2005语法增强之PIVOT使用 PIVOT 和 UNPIVOT 可以使用 PIVOT 和 UNPIVOT 关系运
浅谈SQLServer行列转换PIVOT函数的使用
Wikey_Zhang的博客
08-04 1万+
以学生表举个例子，展现学生的各门学科和成绩，我们先新建一张表： Create Table Students(Name varchar(10), Subject Nvarchar(10),Score int) Insert into Students Select 'Andy' ,'Chiness',round(60+40*rand(),0) Union all Select 'Bur
这里是测试的export.zip
08-22
这里是测试的export.zip
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值