有一个表如下：
Name Number Address
A 1 1
A 2 3
B 2 2
C 4 4
........
要求用sql 输出如下：
Name Address_1 Address_2 Address_3 Address_4
A 1 0 2 0
B 0 0 2 0
C 0 0 0 4
........
--如果只有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个的
