select msc, bsc, aep, trim(max(sys_connect_by_path(bts, ' ')))
from (
select msc, bsc, aep, bts, row_number() over (partition by msc, bsc, aep order by port_n) part_rank
from lin
)
start with part_rank = 1
connect by prior msc = msc and prior bsc = bsc and prior aep = aep and prior part_rank = part_rank - 1
group by msc, bsc, aep
;
¡¡ |
Msc |
Bsc |
Aep |
Trim(max(sys_Connect_By_Path(bts,''))) |
1 |
MSC101 |
0 |
0 |
18 11 |
2 |
MSC101 |
0 |
1 |
18 12 11 14 15 |
3 |
MSC101 |
0 |
2 |
16 23 |
4 |
MSC101 |
0 |
3 |
24 18 |
5 |
MSC101 |
0 |
4 |
18 11 |
select msc, bsc, aep, max(decode(port_n, 0, bts, '')) p0, max(decode(port_n, 1, bts, '')) p1, max(decode(port_n, 2, bts, '')) p2, max(decode(port_n, 3, bts, '')) p3,
max(decode(port_n, 4, bts, '')) p4, max(decode(port_n, 5, bts, '')) p5, max(decode(port_n, 6, bts, '')) p6, max(decode(port_n, 7, bts, '')) p7
from lin
group by msc, bsc, aep;
¡¡ |
Msc |
Bsc |
Aep |
P0 |
P1 |
P2 |
P3 |
P4 |
P5 |
P6 |
P7 |
1 |
MSC101 |
0 |
0 |
18 |
¡¡ |
¡¡ |
¡¡ |
¡¡ |
11 |
¡¡ |
¡¡ |
2 |
MSC101 |
0 |
1 |
18 |
¡¡ |
¡¡ |
¡¡ |
12 |
11 |
14 |
15 |
3 |
MSC101 |
0 |
2 |
16 |
¡¡ |
¡¡ |
¡¡ |
¡¡ |
¡¡ |
¡¡ |
23 |
4 |
MSC101 |
0 |
3 |
24 |
18 |
¡¡ |
¡¡ |
¡¡ |
¡¡ |
¡¡ |
¡¡ |
5 |
MSC101 |
0 |
4 |
18 |
¡¡ |
¡¡ |
¡¡ |
¡¡ |
11 |
¡¡ |
¡¡ |