表结构以及数据如下
item huadong huanan huabei
-------------------------------------------
saleAmt 100 200 300
orderCount 2 3 4
表示某个产品华东区,华南区,以及华北区的各项kpi的情况
想查询出的结果为quyu saleAmt orderCount
huadong 100 2
huanan 200 3
huabei 300 4简单就是说把表中的记录数变成了列,表中的列变成了行,一直没解决,谢谢各位了
item huadong huanan huabei
-------------------------------------------
saleAmt 100 200 300
orderCount 2 3 4
表示某个产品华东区,华南区,以及华北区的各项kpi的情况
想查询出的结果为quyu saleAmt orderCount
huadong 100 2
huanan 200 3
huabei 300 4简单就是说把表中的记录数变成了列,表中的列变成了行,一直没解决,谢谢各位了
(select 'saleAmt' as item, '100' as huadong,'200' as huanan,'300' as huabei from dual
union all
select 'orderCount' as item, '2' as huadong, '3' as huanan, '4' as huabei from dual
)
select 'huadong' as quyu, sum(decode(item, 'saleAmt', huadong, 0)) as aleAmt, sum(decode(item, 'orderCount', huadong, 0)) as orderCount from temp_tab1 union all
select 'huanan' as quyu, sum(decode(item, 'saleAmt', huanan, 0)) as aleAmt, sum(decode(item, 'orderCount', huanan, 0)) as orderCount from temp_tab1 union all
select 'huabei' as quyu, sum(decode(item, 'saleAmt', huabei, 0)) as aleAmt, sum(decode(item, 'orderCount', huabei, 0)) as orderCount from temp_tab1
sum(decode(item,'saleAmt',huadong)) saleAmt,
sum(decode(item,'orderCount',huadong)) orderCount
from tt
union
select 'huanan' quyu,
sum(decode(item,'saleAmt',huanan)) saleAmt,
sum(decode(item,'orderCount',huanan)) orderCount
from tt
union
select 'huabei' quyu,
sum(decode(item,'saleAmt',huabei)) saleAmt,
sum(decode(item,'orderCount',huabei)) orderCount
from tt
max(case when item='saleAmt' then huadong end ) saleAmt,
max(case when item='orderCount' then huadong end ) orderCount
from tt
union
select 'huanan' quyu,
max(case when item='saleAmt' then huanan end ) saleAmt,
max(case when item='orderCount' then huanan end ) orderCount
from tt
union
select 'huabei' quyu,
max(case when item='saleAmt' then huabei end ) saleAmt,
max(case when item='orderCount' then huabei end ) orderCount
from tt
decode 和case when 是差不多的