Oracle的數(shù)據(jù)表中行轉(zhuǎn)列與列轉(zhuǎn)行的操作實(shí)例講解
行轉(zhuǎn)列
一張表
查詢結(jié)果為
--行轉(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í)代 金額
顯示行轉(zhuǎn)列
姓名 時(shí)代 金額
姓名 年輕 中年 老年
張麗 1000000.00 4000000.00 500000000.00
孫子 2000000.00 12233335.00 4552220010.00
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]
顯示效果
靜態(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)行
生成
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
相關(guān)文章
Oracle實(shí)現(xiàn)行列轉(zhuǎn)換的方法分析
這篇文章主要介紹了Oracle實(shí)現(xiàn)行列轉(zhuǎn)換的方法,結(jié)合實(shí)例形式分析了Oracle針對(duì)固定列、不定列、列數(shù)不固定等情況下的行列轉(zhuǎn)換操作技巧,需要的朋友可以參考下2016-08-08oracle中fdisk導(dǎo)致的ASM磁盤(pán)數(shù)據(jù)丟失的解決方法
oracle中fdisk 導(dǎo)致的ASM磁盤(pán)數(shù)據(jù)丟失 有需要的朋友可參考一下2012-10-10Oracle 11g Release (11.1) 索引底層的數(shù)據(jù)結(jié)構(gòu)
本文介紹關(guān)于 Oracle 索引的結(jié)構(gòu)。大概了解 Oracle 索引底層的數(shù)據(jù)結(jié)構(gòu),從而更好地理解 Oracle 索引對(duì)增、刪、改、查的性能2012-11-11Oracle 11g2的監(jiān)聽(tīng)器配置教程
Oracle的監(jiān)聽(tīng)器服務(wù)注冊(cè)方式有兩種,分別為動(dòng)態(tài)和靜態(tài),本文將詳細(xì)介紹這兩種,需要的朋友可以參考下2012-11-11oracle的導(dǎo)入導(dǎo)出注意事項(xiàng)及心得分享
導(dǎo)入導(dǎo)出oracle方案是備份和恢復(fù)的主旋律,有一點(diǎn)點(diǎn)在使用過(guò)程中的體會(huì),在此與大家分享下,希望對(duì)大家有所幫助2013-09-09Oracle7.X 回滾表空間數(shù)據(jù)文件誤刪除處理方法
Oracle7.X 回滾表空間數(shù)據(jù)文件誤刪除處理方法...2007-03-03Oracle sqlldr導(dǎo)入一個(gè)日期列實(shí)例詳解
這篇文章主要介紹了Oracle sqlldr導(dǎo)入一個(gè)日期列實(shí)例詳解的相關(guān)資料,需要的朋友可以參考下2017-06-06