行列轉換啊!
SQL> with a as (select '張三' NAME,'電話1' PHONE FROM DUAL
2 UNION
3 select '張三' NAME,'電話2' PHONE FROM DUAL
4 UNION
5 select '張三' NAME,'電話3' PHONE FROM DUAL
6 )
7 select NAME,MAX(DECODE(RN,1,PHONE)) PHONE1,
8 MAX(DECODE(RN,2,PHONE)) PHONE2,
9 MAX(DECODE(RN,3,PHONE)) PHONE3
10 --MAX(DECODE(RN,1,PHONE)) PHONE4,
11 --MAX(DECODE(RN,1,PHONE)) PHONE5
12 FROM (SELECT NAME,PHONE,ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY NAME) RN
13 FROM A)
14 GROUP BY NAME
15 /
NAME PHONE1 PHONE2 PHONE3
---- ------ ------ ------
張三 電話1 電話3 電話2
SQL>
SQL> with a as (select '張三' NAME,'電話1' PHONE FROM DUAL
2 UNION
3 select '張三' NAME,'電話2' PHONE FROM DUAL
4 UNION
5 select '張三' NAME,'電話3' PHONE FROM DUAL
6 )
7 select NAME,MAX(DECODE(RN,1,PHONE)) PHONE1,
8 MAX(DECODE(RN,2,PHONE)) PHONE2,
9 MAX(DECODE(RN,3,PHONE)) PHONE3
10 --MAX(DECODE(RN,1,PHONE)) PHONE4,
11 --MAX(DECODE(RN,1,PHONE)) PHONE5
12 FROM (SELECT NAME,PHONE,ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY NAME) RN
13 FROM A)
14 GROUP BY NAME
15 /
NAME PHONE1 PHONE2 PHONE3
---- ------ ------ ------
張三 電話1 電話3 電話2
SQL>
select NAME,MAX(DECODE(RN,1,PHONE)) PHONE1,
MAX(DECODE(RN,2,PHONE)) PHONE2,
MAX(DECODE(RN,3,PHONE)) PHONE3,
MAX(DECODE(RN,1,PHONE)) PHONE4,
MAX(DECODE(RN,1,PHONE)) PHONE5
FROM (SELECT NAME,PHONE,ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY NAME) RN
FROM A)
GROUP BY NAME
------------------------------ ------------------------------
张三 88866666
张三 88866667
张三 88866668
张三 88866669
张三 88866660
张四 88166666
张四 88266666
张四 88366666
张四 88466666
张四 88566666
张四 88666666
张四 8876666612 rows selectedSQL> select u_name,max(n_1) as n1,max(n_2) as n2,max(n_3) as n3,max(n_4) as n4,max(n_5) as n5 from
2 (
3 select u_name,decode(sn,1,u_number,null) as n_1,
4 decode(sn,2,u_number,null) as n_2,
5 decode(sn,3,u_number,null) as n_3,
6 decode(sn,4,u_number,null) as n_4,
7 decode(sn,5,u_number,null) as n_5
8 from
9 (
10 select u_name,u_number,sn from
11 (select u_name,u_number,row_number() over (partition by u_name order by u_number) as sn from test_tt)
12 where sn < 6
13 )
14 ) group by u_name;U_NAME N1 N2 N3 N4 N5
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
张三 88866660 88866666 88866667 88866668 88866669
张四 88166666 88266666 88366666 88466666 88566666SQL>
我多了rn<=5的条件,不过不加也没关系的