Oracle的數(shù)據(jù)表中行轉列與列轉行的操作實例講解
行轉列
一張表
查詢結果為
--行轉列
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.人員信息表包括姓名 時代 金額
顯示行轉列
姓名 時代 金額
姓名 年輕 中年 老年
張麗 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.學生表 [Tb_Student]
顯示效果
靜態(tài)SQL,指subject只有語文、數(shù)學、英語這三門課程。
select sname as 姓名, max(case Subject when '語文' then grade else 0 end) as 語文, max(case Subject when '數(shù)學' then grade else 0 end) as 數(shù)學, max(case Subject when '英語' then grade else 0 end) as 英語 from dbo.Tb_Student group by sname order by sname desc
--動態(tài)SQL,指subject不止語文、數(shù)學、英語這三門課程。
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ù)使用 然后將這些累計求和(sum部分)
select t.sname AS 姓名, sum(decode(t.subject,'語文',grade,null))語文 , sum(decode(t.subject,'數(shù)學',grade,null)) 數(shù)學, sum(decode(t.subject,'英語',grade,null)) 英語 from Tb_Student t group by sname order by sname desc
列轉行
生成
sql代碼
生成靜態(tài):
select * from (select sname,[Course ] ='數(shù)學',[Score]=[數(shù)學] 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 '數(shù)學' then 2 when '英語' then 3 end go --列轉行的靜態(tài)方案:UNPIVOT,sql2005及以后版本 SELECT sname,Subject, grade from dbo.Tb_students unpivot(grade for Subject in([語文],[數(shù)學],[英語]))as up GO --列轉行的動態(tài)方案:UNPIVOT,sql2005及以后版本 --因為行是動態(tài)所以這里就從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([數(shù)學],[英語],[語文]))b
相關文章
oracle中fdisk導致的ASM磁盤數(shù)據(jù)丟失的解決方法
oracle中fdisk 導致的ASM磁盤數(shù)據(jù)丟失 有需要的朋友可參考一下2012-10-10Oracle 11g Release (11.1) 索引底層的數(shù)據(jù)結構
本文介紹關于 Oracle 索引的結構。大概了解 Oracle 索引底層的數(shù)據(jù)結構,從而更好地理解 Oracle 索引對增、刪、改、查的性能2012-11-11Oracle7.X 回滾表空間數(shù)據(jù)文件誤刪除處理方法
Oracle7.X 回滾表空間數(shù)據(jù)文件誤刪除處理方法...2007-03-03