I wonder if there is a better (i.e. faster execution) solution to the problem described below.
Step 1)
create table t (k number, v1 number, v2 number);
insert into t values (1,1,1);
insert into t values (1,2,2);
insert into t values (1,2,3);
insert into t values (1,3,3);
insert into t values (1,4,3);
insert into t values (2,7,8);
insert into t values (2,7,9);
Step 2)
I would like to return the following data set
(k, v1_list, v2_list)
1, (1,2,3,4), (1,2,3)
2, (7), (8,9)
Step 3)
I am able to solve it by running the multiple combination of listagg() and I wonder if it can be achieved in another way. In this example I deal with 2 attributes but in reality I have to run the similar statements for hundreds of lists. Querying as shown below may become combersome. I wonder if it can be done in one query, possibly using listagg() several times?
with q1 as (
select distinct k, listagg (v1, ', ') within group (order by k) over (partition by k) v1_list
from t
group by k, v1),
q2 as (
select distinct k, listagg (v2, ', ') within group (order by k) over (partition by k) v2_list
from t
group by k, v2)
--
select distinct t.k, v1_list, v2_list from t
--
join q1 on q1.k = t.k
join q2 on q2.k = t.k
I appreciate your advice,
-- Lukas
You can try undocumented function
wm_concat
or with
listagg