服务器之家

服务器之家 > 正文

Oracle的数据表中行转列与列转行的操作实例讲解

时间:2019-12-24 13:55     来源/作者:真正男子汉

行转列
一张表

Oracle,数据表,行转列,列转行

查询结果为

Oracle,数据表,行转列,列转行

--行转列

?
1
2
3
4
select years,(select amount from Tb_Amount as A where month=1 and A.years=Tb_Amount.years)as m1,
(select amount from Tb_Amount as A where month=2 and A.years=Tb_Amount.years)as m2,
(select amount from Tb_Amount as A where month=3 and A.years=Tb_Amount.years)as m3
from Tb_Amount group by years

或者为

?
1
2
3
4
5
select years as 年份,
sum(case when month='1' then amount end) as 一月,
 sum(case when month='2' then amount end) as 二月,
sum(case when month='3' then amount end) as 三月
from dbo.Tb_Amount group by years order by years desc

2.人员信息表包括姓名 时代  金额

Oracle,数据表,行转列,列转行

显示行转列
姓名     时代       金额

姓名  年轻         中年       老年

张丽 1000000.00 4000000.00    500000000.00

孙子 2000000.00   12233335.00  4552220010.00

Oracle,数据表,行转列,列转行

?
1
2
3
4
5
select uname as 姓名,
SUM(case when era='年轻' then amount end) as 年轻,
SUM(case when era='中年' then amount end) as 中年,
SUM(case when era='老年' then amount end) as 老年
from Tb_People group by uname order by uname desc

 3.学生表 [Tb_Student]

Oracle,数据表,行转列,列转行

显示效果

Oracle,数据表,行转列,列转行

静态SQL,指subject只有语文、数学、英语这三门课程。

?
1
2
3
4
5
select sname as 姓名,
max(case Subject when '语文' then grade else 0 end) as 语文,
max(case Subject when '数学' then grade else 0 end) as 数学,
max(case Subject when '英语' then grade else 0 end) as 英语
from dbo.Tb_Student group by sname order by sname desc

--动态SQL,指subject不止语文、数学、英语这三门课程。

?
1
2
3
4
5
6
declare @sql varchar(8000)
set @sql = 'select sname as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then grade else 0 end) [' + Subject + ']'
from (select distinct Subject from Tb_Student) as a
set @sql = @sql + ' from Tb_Student group by sname order by sname desc'
exec(@sql)

oracle中Decode()函数使用 然后将这些累计求和(sum部分)

?
1
2
3
4
5
select t.sname AS 姓名,
sum(decode(t.subject,'语文',grade,null))语文 ,
sum(decode(t.subject,'数学',grade,null)) 数学,
sum(decode(t.subject,'英语',grade,null)) 英语
from Tb_Student t group by sname order by sname desc


列转行

Oracle,数据表,行转列,列转行

生成

Oracle,数据表,行转列,列转行

sql代码
生成静态:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
select *
from (select sname,[Course ] ='数学',[Score]=[数学] from Tb_students union all
select sname,[Course]='英语',[Score]=[英语] from Tb_students union all
select sname,[Course]='语文',[Score]=[语文] from Tb_students)t
order by sname,case [Course] when '语文' then 1 when '数学' then 2 when '英语' then 3 end
go
 --列转行的静态方案:UNPIVOT,sql2005及以后版本
 
 SELECT sname,Subject, grade
 from dbo.Tb_students
 unpivot(grade for Subject in([语文],[数学],[英语]))as up
 GO
 
 
 --列转行的动态方案:UNPIVOT,sql2005及以后版本
 --因为行是动态所以这里就从INFORMATION_SCHEMA.COLUMNS视图中获取列来构造行,同样也使用了XML处理。
 declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Tb_students') and Name not in('sname')
order by Colid
exec('select sname,[Subject],[grade] from Tb_students unpivot ([grade] for [Subject] in('+@s+'))b')
 
go
select
  sname,[Subject],[grade]
from
  Tb_students
unpivot
  ([grade] for [Subject] in([数学],[英语],[语文]))b

 

相关文章

热门资讯

玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
配置IIS网站web服务器的安全策略配置解决方案
配置IIS网站web服务器的安全策略配置解决方案 2019-05-23
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
Nginx服务器究竟是怎么执行PHP项目
Nginx服务器究竟是怎么执行PHP项目 2019-05-24
返回顶部