现有一张表,表名为:phone 数据如下
id nmae phnum
1 w 13754321550
2 w 86695481
3 y 13095715619
4 y 13788888871
select name phnum from phone
结果如下:
name phnum
w 13754321550
w 86695481
y 13095715619
y 13788888871
我想得到如下结果SQL语句怎么写:
name phnum
w 13754321550/86695481
y 13095715619/13788888871
id nmae phnum
1 w 13754321550
2 w 86695481
3 y 13095715619
4 y 13788888871
select name phnum from phone
结果如下:
name phnum
w 13754321550
w 86695481
y 13095715619
y 13788888871
我想得到如下结果SQL语句怎么写:
name phnum
w 13754321550/86695481
y 13095715619/13788888871
from(
select name,WMSYS.WM_CONCAT(phnum) phnum
from phone
group by name
) a
2 union
3 select 'w' name,86695481 phnum from dual
4 union
5 select 'y' name,13095715619 phnum from dual
6 union
7 select 'y' name,13788888871 phnum from dual
8 )
9 select name,substr(max(sys_connect_by_path(phnum,',')),2) phnum
10 from (select a.*,row_number()over(partition by name order by name) rn from a )
11 group by name
12 start with rn=1
13 connect by rn-1=prior rn and name=prior name
14 /
NAME PHNUM
---- --------------------------------------------------------------------------------
w 86695481,13754321550
y 13095715619,13788888871