Oracle的數(shù)據(jù)表中行轉(zhuǎn)列與列轉(zhuǎn)行的操作實(shí)例講解

更新時(shí)間：2015年12月17日 17:19:41 作者：真正男子漢

這篇文章主要介紹了Oracle數(shù)據(jù)表中行轉(zhuǎn)列與列轉(zhuǎn)行的操作方法,這里分靜態(tài)和動(dòng)態(tài)情況作出了分類討論,需要的朋友可以參考下

行轉(zhuǎn)列
一張表

20151217170849821.jpg (220×151)

查詢結(jié)果為

20151217170911011.jpg (170×63)

--行轉(zhuǎn)列

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.人員信息表包括姓名時(shí)代金額

20151217170947066.jpg (254×150)

顯示行轉(zhuǎn)列
姓名時(shí)代金額

姓名年輕中年老年

張麗 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.學(xué)生表 [Tb_Student]

20151217171053471.jpg (204×144)

顯示效果

20151217171109012.jpg (191×56)

靜態(tài)SQL,指subject只有語(yǔ)文、數(shù)學(xué)、英語(yǔ)這三門(mén)課程。

select sname as 姓名,
max(case Subject when '語(yǔ)文' then grade else 0 end) as 語(yǔ)文,
max(case Subject when '數(shù)學(xué)' then grade else 0 end) as 數(shù)學(xué),
max(case Subject when '英語(yǔ)' then grade else 0 end) as 英語(yǔ)
from dbo.Tb_Student group by sname order by sname desc

--動(dòng)態(tài)SQL,指subject不止語(yǔ)文、數(shù)學(xué)、英語(yǔ)這三門(mén)課程。

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()函數(shù)使用然后將這些累計(jì)求和(sum部分）

select t.sname AS 姓名,
sum(decode(t.subject,'語(yǔ)文',grade,null))語(yǔ)文 ,
sum(decode(t.subject,'數(shù)學(xué)',grade,null)) 數(shù)學(xué),
sum(decode(t.subject,'英語(yǔ)',grade,null)) 英語(yǔ)
from Tb_Student t group by sname order by sname desc

列轉(zhuǎn)行

20151217171127272.jpg (225×66)

生成

20151217171144405.jpg (223×134)

sql代碼
生成靜態(tài):

select *
from (select sname,[Course ] ='數(shù)學(xué)',[Score]=[數(shù)學(xué)] from Tb_students union all
select sname,[Course]='英語(yǔ)',[Score]=[英語(yǔ)] from Tb_students union all
select sname,[Course]='語(yǔ)文',[Score]=[語(yǔ)文] from Tb_students)t
order by sname,case [Course] when '語(yǔ)文' then 1 when '數(shù)學(xué)' then 2 when '英語(yǔ)' then 3 end
go
 --列轉(zhuǎn)行的靜態(tài)方案：UNPIVOT,sql2005及以后版本
 
 SELECT sname,Subject, grade
 from dbo.Tb_students
 unpivot(grade for Subject in([語(yǔ)文],[數(shù)學(xué)],[英語(yǔ)]))as up
 GO
 
 
 --列轉(zhuǎn)行的動(dòng)態(tài)方案：UNPIVOT,sql2005及以后版本
 --因?yàn)樾惺莿?dòng)態(tài)所以這里就從INFORMATION_SCHEMA.COLUMNS視圖中獲取列來(lái)構(gòu)造行，同樣也使用了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([數(shù)學(xué)],[英語(yǔ)],[語(yǔ)文]))b

您可能感興趣的文章: