mysql实现行转列，列转行

一： 行转列

部门	金额	类型
001	100	aaa
001	50	aaa
002	100	eee
003	40	bbb
004	300	ccc

部门	aaa	bbb	ccc	eee
001	150
002				100
003		40
004			300

可以看出，这里行转列是将原来的类型字段的多行内容选出来，作为结果集中的不同列，并根据部门进行分组显示对应的金额。

select 部门，

sum(case when 类型 = 'aaa' then 金额 end) as aaa,

sum(case when 类型 = 'bbb' then 金额 end) as bbb,

sum(case when 类型 = 'ccc' then 金额 end) as ccc,

sum(case when 类型 = 'eee' then 金额 end) as eee

from (

select '001' as 部门，100 as 金额，'aaa' as 类型 from dual

union all

select '001' as 部门，50 as 金额，'aaa' as 类型 from dual

union all

select '002' as 部门，100 as 金额，'eee' as 类型 from dual

union all

select '003' as 部门，40 as 金额，'bbb' as 类型 from dual

union all

select '004' as 部门，300 as 金额，'ccc' as 类型 from dual

)  tempTb group by 部门

说明：粉色部分是模拟一张表 tempTb

二：列转行

部门	aaa	bbb	ccc	eee
001	100
001	50
002				100
003		40
004			300

部门	金额	类型
001	100	aaa
001	50	aaa
002	100	eee
003	40	bbb
004	300	ccc

可以看出，这里列转行是将原来的多个不同列名选出来，作为结果集中的同一列，并根据部门进行分组显示对应的金额。

select 部门, aaa as '金额' ,'aaa' as '类型'
from (
 select '001' as 部门，'100' as aaa, '' as bbb, '' as ccc, '' as eee from dual
 union all 
 select '001' as 部门，'50' as aaa, '' as bbb, '' as ccc, '' as eee from dual
 union all
 select '002' as 部门，'' as aaa, '' as bbb, '' as ccc, '100' as eee from dual
 union all
 select '003' as 部门，'' as aaa, '40' as bbb, '' as ccc, '' as eee from dual
 union all
 select '004' as 部门，'' as aaa, '' as bbb, '300' as ccc, '' as eee from dual
)  tempTb where aaa <> ''
union all
select 部门, eee as '金额' ,'eee' as '类型'
from (
 select '001' as 部门，'100' as aaa, '' as bbb, '' as ccc, '' as eee from dual
 union all 
 select '001' as 部门，'50' as aaa, '' as bbb, '' as ccc, '' as eee from dual
 union all
 select '002' as 部门，'' as aaa, '' as bbb, '' as ccc, '100' as eee from dual
 union all
 select '003' as 部门，'' as aaa, '40' as bbb, '' as ccc, '' as eee from dual
 union all
 select '004' as 部门，'' as aaa, '' as bbb, '300' as ccc, '' as eee from dual
)  tempTb where eee <> ''
union all
select 部门, bbb as '金额' ,'bbb' as '类型'
from (
 select '001' as 部门，'100' as aaa, '' as bbb, '' as ccc, '' as eee from dual
 union all 
 select '001' as 部门，'50' as aaa, '' as bbb, '' as ccc, '' as eee from dual
 union all
 select '002' as 部门，'' as aaa, '' as bbb, '' as ccc, '100' as eee from dual
 union all
 select '003' as 部门，'' as aaa, '40' as bbb, '' as ccc, '' as eee from dual
 union all
 select '004' as 部门，'' as aaa, '' as bbb, '300' as ccc, '' as eee from dual
)  tempTb where bbb <> ''
union all
select 部门, ccc as '金额' ,'ccc' as '类型'
from (
 select '001' as 部门，'100' as aaa, '' as bbb, '' as ccc, '' as eee from dual
 union all 
 select '001' as 部门，'50' as aaa, '' as bbb, '' as ccc, '' as eee from dual
 union all
 select '002' as 部门，'' as aaa, '' as bbb, '' as ccc, '100' as eee from dual
 union all
 select '003' as 部门，'' as aaa, '40' as bbb, '' as ccc, '' as eee from dual
 union all
 select '004' as 部门，'' as aaa, '' as bbb, '300' as ccc, '' as eee from dual
) tempTb  where ccc <> ''

说明：粉色部分是模拟一张表 tempTb