表a(A,B,C)
data:
A B C
--------------------------
a1 2 3
a2 3 4
a3 0 1
现在要转化成:
X a1 a2 a3
-------------------------
B 2 3 0
C 3 4 1 能否用SQL来实现?
data:
A B C
--------------------------
a1 2 3
a2 3 4
a3 0 1
现在要转化成:
X a1 a2 a3
-------------------------
B 2 3 0
C 3 4 1 能否用SQL来实现?
union all
select 'c x,sum(decode(a,'a1',c,0)a1,sum(decode(a,'a2',c,0)a2,sum(decode(a,'a3',c,0) a3
select 'b' x,sum(decode(a,'a1',b,0)) a1,sum(decode(a,'a2',b,0)) a2,sum(decode(a,'a3',b,0)) a3
union all
select 'c' x,sum(decode(a,'a1',c,0)) a1,sum(decode(a,'a2',c,0)) a2,sum(decode(a,'a3',c,0)) a3
select 'B' x,sum(decode(a,'a1',b,0)) a1,sum(decode(a,'a2',b,0)) a2,sum(decode(a,'a3',b,0)) a3
from data
union all
select 'C' x,sum(decode(a,'a1',c,0)) a1,sum(decode(a,'a2',c,0)) a2,sum(decode(a,'a3',c,0)) a3
from
data测试语句
select 'B' x,sum(decode(a,'a1',b,0)) a1,sum(decode(a,'a2',b,0)) a2,sum(decode(a,'a3',b,0)) a3
from (
select 'a1' a, 2 b, 3 c from dual
union all
select 'a2' a, 3 b, 4 c from dual
union all
select 'a3' a, 0 b, 1 c from dual)
union all
select 'C' x,sum(decode(a,'a1',c,0)) a1,sum(decode(a,'a2',c,0)) a2,sum(decode(a,'a3',c,0)) a3
from
(
select 'a1' a, 2 b, 3 c from dual
union all
select 'a2' a, 3 b, 4 c from dual
union all
select 'a3' a, 0 b, 1 c from dual)结果
Row# X A1 A2 A31 B 2 3 0
2 C 3 4 1
轻松实现行列转换
sum(decode(A,'a1',b,null))a1,
sum(decode(A,'a2',b,null))a2,
sum(decode(A,'a3',b,null))a3
from 表a
union
select max(decode(B,'-','--','c'))Z,
sum(decode(A,'a1',c,null))a1,
sum(decode(A,'a2',c,null))a2,
sum(decode(A,'a3',c,null))a3
from 表a