欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-26 22:48:46
行转列 一张表 查询结果为 --行转列 select years,(select amount from tb_amount as a...

行转列
一张表

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

查询结果为

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

--行转列

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

或者为

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的数据表中行转列与列转行的操作实例讲解

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只有语文、数学、英语这三门课程。

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不止语文、数学、英语这三门课程。

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部分)

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代码
生成静态:

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