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 

 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 

 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 

 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) 

 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 

 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