当前位置： 移动技术网 > IT编程>数据库>Oracle > Oracle的数据表中行转列与列转行的操作实例讲解

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

2017年12月12日  | 移动技术网IT编程  | 我要评论

行转列
一张表

20151217170849821.jpg (220×151)

查询结果为

20151217170911011.jpg (170×63)

--行转列

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.人员信息表包括姓名 时代  金额

20151217170947066.jpg (254×150)

显示行转列
姓名     时代       金额

姓名  年轻         中年       老年

张丽 1000000.00 4000000.00    500000000.00

孙子 2000000.00   12233335.00  4552220010.00

20151217171005767.jpg (322×84)

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]

20151217171053471.jpg (204×144)

显示效果

20151217171109012.jpg (191×56)

静态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


列转行

20151217171127272.jpg (225×66)

生成

20151217171144405.jpg (223×134)

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

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网