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