解决方案 »
- Oracle 11G客户端配置问题
- 求一条关于合并内容的sql语句
- 自定义记录报错:PLS-00103: Encountered the symbol "NAME_TYPE" when expecting one of the
- insert语句问题
- Oracle9i中如何创建一个普通用户
- 求oracle类似split的函数
- 问一个sql语句
- sql语句,查出次数最多的值 ?
- 求一个替换字符串的正则表达式01!01.01!01.01.01变成01.02!01.02.01!01.02.01.01
- 关于trunc(sysdate)的问题
- 求助下ORACLE的变量设置与应用
- oracle 根据当前时间+时间段=时间期限问题
--如果只有4个地址的话:
select name,max(decode(addr,1,num,0)) addr1,
max(decode(addr,2,num,0)) addr2,
max(decode(addr,3,num,0)) addr3,
max(decode(addr,4,num,0)) addr3
from t
group by name
SQL> with t as(
2 select 'A' name,1 num,1 addr from dual union all
3 select 'A',2,3 from dual union all
4 select 'B',2,2 from dual union all
5 select 'C',4,4 from dual)
6 select name,max(decode(addr,1,num,0)) addr1,
7 max(decode(addr,2,num,0)) addr2,
8 max(decode(addr,3,num,0)) addr3,
9 max(decode(addr,4,num,0)) addr3
10 from t
11 group by name
12 /
NAME ADDR1 ADDR2 ADDR3 ADDR3
---- ---------- ---------- ---------- ----------
A 1 0 2 0
B 0 2 0 0
C 0 0 0 4
--是几个就多加几个max子句吧
select name,max(decode(addr,1,num,0)) addr1,
max(decode(addr,2,num,0)) addr2,
max(decode(addr,3,num,0)) addr3,
max(decode(addr,4,num,0)) addr4,
max(decode(addr,5,num,0)) addr5,
max(decode(addr,6,num,0)) addr6
from t
group by name
--这是一种不灵活的写法,但是实际中,某个name下的address也不会达到7、8个的若要使用动态的写法,请参考:
http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html?53031