问题描述

SQL SERVER中行列转换的问题

原数据表
id num acount
0 52 1826.52
1 60 2095.11
2 63 2235.61
3 60 2091.12
4 52 1797.13
5 59 2094.63

如何变成目标数据表
id 0 1 2 3 4 5
num 52 60 63 60 52 59
acount 1826.52 2095.11 2235.61 2091.12 1797.13 2094.63

解决方案

类似下面这样，没有调试

 select sum(row1) row1,sum(row2) row2,sum(row3) row3,sum(row4) row4,sum(row5) row5,sum(row6) row6
from(
select case id when 0 then id row1
,case id when 1 then id row2
,case id when 2 then id row3
,case id when 3 then id row4
,case id when 4 then id row5
,case id when 5 then id row6
from 表 group by id
)
union
select sum(row1) row1,sum(row2) row2,sum(row3) row3,sum(row4) row4,sum(row5) row5,sum(row6) row6
from(
select case id when 0 then num row1
,case id when 1 then num row2
,case id when 2 then num row3
,case id when 3 then num row4
,case id when 4 then num row5
,case id when 5 then num row6
)
from 表 group by id
union
select sum(row1) row1,sum(row2) row2,sum(row3) row3,sum(row4) row4,sum(row5) row5,sum(row6) row6
from(
select case id when 0 then num row1
,case id when 1 then num row2
,case id when 2 then num row3
,case id when 3 then num row4
,case id when 4 then num row5
,case id when 5 then num row6
from 表 group by id
)

解决方案二：

select 'num', sum(decode(id,0,acount)) a0,
sum(decode(id,1,num)) a1,
sum(decode(id,2,num)) a2,
sum(decode(id,3,num)) a3,
sum(decode(id,4,num))a4,
sum(decode(id,5,num)) a5
from mid_pbsh2
union all
select 'acount', sum(decode(id,0,acount)) a0,
sum(decode(id,1,acount)) a1,
sum(decode(id,2,acount)) a2,
sum(decode(id,3,acount)) a3,
sum(decode(id,4,acount))a4,
sum(decode(id,5,acount)) a5
from mid_pbsh2;