1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
create table pivot (
r number
)
/
insert into pivot
select rownum from all_objects
where rownum <= 10
/
create table t_tab (
id number
)
/
insert into t_tab values(12345)
/
insert into t_tab values(12346)
/
create table t_act (
id number,
cod_a varchar2(2)
)
/
insert into t_act values(12345, 'A1')
/
insert into t_act values(12345, 'A2')
/
insert into t_act values(12346, 'A1')
/
create table t_canal (
id number,
cod_c varchar2(2)
)
/
insert into t_canal values(12345, 'C1')
/
insert into t_canal values(12346, 'C1')
/
insert into t_canal values(12346, 'C2')
/
create table t_ip (
id number,
cod_i varchar2(2)
)
/
insert into t_ip values(12345, 'I1')
/
insert into t_ip values(12345, 'I2')
/
commit
/
mni@DIANA> Select t.id, a.cod_a, c.cod_c, i.cod_i
2 from
3 (select id, r from t_tab, pivot
4 ) t,
5 (select id, cod_a, row_number() over (partition by id order by cod_a) r
6 from t_act
7 ) a,
8 (select id, cod_c, row_number() over (partition by id order by cod_c) r
9 from t_canal
10 ) c,
11 (select id, cod_i, row_number() over (partition by id order by cod_i) r
12 from t_ip) i
13 Where t.id = a.id(+)
14 And t.r = a.r(+)
15 And t.id = c.id(+)
16 And t.r = c.r(+)
17 And t.id = i.id(+)
18 And t.r = i.r(+)
19 And (a.cod_a Is not null or c.cod_c is not null or i.cod_i is not null)
20 /
ID CO CO CO
---------- -- -- --
12345 A1 C1 I1
12345 A2 I2
12346 A1 C1
12346 C2 |
