行轉列一張表

查詢結果為

--行轉列

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]

顯示效果

靜態SQL,指subject只有語文、數學、英語這三門課程。

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

--動態SQL,指subject不止語文、數學、英語這三門課程。

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

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

列轉行

生成

sql代碼

生成靜態:

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

總結

以上是生活随笔為你收集整理的oracle+行换列,Oracle的数据表中行转列与列转行的操作实例讲解的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。