For example, for the table:
SQL> select * from t;
KEY VALUE
---------- --------------------
1 a
1 b
1 c
How to write a sql to give the output looks like:
KEY VALUE
--- -----
1 a/b/c
He actually gave the answer. Based on his input, I did a test. First I created the following table:
SQL> create table uavsub (num_prod number,
2 summary number,
3 cod_dep varchar2(20)
4 );
Table created.
SQ>
SQL> insert into uavsub values (3, 3, 'a');
1 row created.
SQL> insert into uavsub values (2, 9, 'b');
1 row created.
SQL> insert into uavsub values (1, 1, 'a');
1 row created.
SQL> insert into uavsub values (1, 3, 'a');
1 row created.
SQL> insert into uavsub values (3, 4, 'a');
1 row created.
SQL> insert into uavsub values (2, 1, 'a');
1 row created.
SQL> insert into uavsub values (3, 1, 'a');
1 row created.
SQL> insert into uavsub values (3, 2, 'a');
1 row created.
SQL> insert into uavsub values (1, 2, 'b');
1 row created.
SQL> insert into uavsub values (3, 3, 'a');
1 row created.
SQL> commit;
Commit complete.
SQ>
SQL> col summary format 999
SQL> select * from uavsub;
NUM_PROD SUMMARY COD_DEP
-------- ------- ---------
3 3 a
2 9 b
1 1 a
1 3 a
3 4 a
2 1 a
3 1 a
3 2 a
1 2 b
3 3 a
10 rows selected.
Then I executed the following sql, which only gave ouput for the case of num_prod=3:
SQL>
SQL> select t.num_prod num_prod,
2 max(substr(sys_connect_by_path(t.summary, '/'), 2)) summary
3 from (
4 select num_prod,
5 summary,
6 row_number() over (partition by num_prod order by summary ) rn
7 from uavsub
8 where num_prod=3
9 and cod_dep is not null
10 ) t
11 start with rn = 1
12 connect by rn = prior rn + 1
13 and num_prod = prior num_prod
14 group by t.num_prod;
NUM_PROD SUMMARY
-------- ------
3 1/2/3/3/4
The below sql lift the constraint: num_prod=3
SQL>
SQL>
SQL> col summary format a20
SQL>
SQL> select t.num_prod num_prod,
2 max(substr(sys_connect_by_path(t.summary, '/'), 2)) summary
3 from (
4 select num_prod,
5 summary,
6 row_number() over (partition by num_prod order by summary ) rn
7 from uavsub
8 where cod_dep is not null
9 ) t
10 start with rn = 1
11 connect by rn = prior rn + 1
12 and num_prod = prior num_prod
13 group by t.num_prod;
NUM_PROD SUMMARY
-------- --------------------
1 1/2/3
2 1/9
3 1/2/3/3/4
However, I did not fully understand the "connect by" sql at that time. I don't know if this is the best way to solve the problem, but this did urge me to understand hierarchical sql better. So I did a few more tests as follows:
For brevity, I create a view first
SQL> create view t_vw as
2 select key, value, row_number() over (partition by key order by value) rn from t;
View created.
SQL> select * from t_vw;
KEY VALUE RN
---------- --------- ----------
1 a 1
1 b 2
1 c 3
Then, I tested the meaning of sys_connect_by_path,
SQL>;
1 select key, value, sys_connect_by_path(value, '/') path from t_vw
2 start with rn = 1
3* connect by rn = prior rn + 1
SQL>
KEY VALUE PATH
---------- -------------------- ----------
1 a /a
1 b /a/b
1 c /a/b/c
Then:
SQL> ;
1 select key, max(sys_connect_by_path(value, '/')) path from t_vw
2 start with rn = 1
3 connect by rn = prior rn + 1
4* group by key
SQL> /
KEY PATH
---------- ------------------------------
1 /a/b/c
Finally,
1 select key, substr(max(sys_connect_by_path(value, '/')),2) path from t_vw
2 start with rn = 1
3 connect by rn = prior rn + 1
4* group by key
SQL> /
KEY PATH
---------- ------------------------------
1 a/b/c
No comments:
Post a Comment