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

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

2019-12-24 13:55真正男子汉 Oracle

这篇文章主要介绍了Oracle数据表中行转列与列转行的操作方法,这里分静态和动态情况作出了分类讨论,需要的朋友可以参考下

行转列
一张表

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