oracle 豎表,橫表(a stupid way,want the smart way)

豎表->橫表

select a.user_ohr_id sso, max(decode(a.change_type_id,1, b.change_type_desc,'')) t1, max(decode(a.change_type_id,2, b.change_type_desc,'')) t2, max(decode(a.change_type_id,3, b.change_type_desc,'')) t3, max(decode(a.change_type_id,4, b.change_type_desc,'')) t4, max(decode(a.change_type_id,5, b.change_type_desc,'')) t5, max(decode(a.change_type_id,6, b.change_type_desc,'')) t6, max(decode(a.change_type_id,7, b.change_type_desc,'')) t7 from occr_user_changetype a ,occr_change_type b where a.change_type_id = b.change_type_id group by a.user_ohr_id

橫表->豎表

select change_type_id,change_type_desc from occr_change_type
union all
select change_type_id,change_type_detail from occr_change_type
order by change_type_id

another way:(complex)

Create table test (name char(10),km char(10),cj int)
go
insert test values('張三','語文',80)
insert test values('張三','數學',86)
insert test values('張三','英語',75)
insert test values('李四','語文',78)
insert test values('李四','數學',85)
insert test values('李四','英語',78)

想變成

姓名 語文 數學 英語
張三 80 86 75
李四 78 85 78


declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)

drop table test
drop table table1
go

[@more@]