行列轉換啊!
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>
解决方案 »
- decode替换 有空格问题
- 一条sql求救,比较急
- 在oracle中,用SQL语句怎样将一个表的所有列标题用逗号连接起来,组成一个字符串,并去掉最后一个逗号?
- 如何将oracle数据导入到excle中
- 急。。急。。急。。安装Oracle10g的客户端时,总是不成功,各位帮忙看看!
- 各位大侠好,可我按这样在sqlplus下运行了1111.sql ( http://www.jzfybjy.com/liou/sccx/uploadfile/sql.txt ) 可在OEM上还是找不到
- 早搞定,早给分!!谢谢!
- 怎样写这个sql语句?多谢
- Solaris2.6上安装Oracle8.1.6的问题
- Oracle 10g 触发器中如何获取Date类型字段的变化值,要有时分秒
- 自己写了一个Procedure,问题不少,大家帮忙挑挑错误,谢了
- 写一个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的条件,不过不加也没关系的