表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来实现?
解决方案 »
- oracle如何填充(insert into)嵌套表(nested table)?
- Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169869568)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
- 数据库过程包加密问题,请高手指点,急急急!
- oracle9i中转义字符的问题!
- 如何delete
- print时碰到"字符串缓冲太小"的问题,快被郁闷坏了.
- oracle 9i R2 可以对数据库做整体加密吗?
- 兄弟们,看看这个存储过程都有些什么语法错误,谢谢.
- Oracle817为什么创建数据库失败?
- proc 编译问题
- ORACLE 数据实时同步的问题。
- oracle10g 如何减少执行 SUM 计算的时间呢?
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