CODEWITHSUNDEEP
stringoracle-databaseaggregate-functionsstring-concatenationlistagg
Lukas
Lukas

Reputation: 29

Oracle LISTAGG() for multiple attributes?

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

Upvotes: 2

Views: 4403

Answers (3)

ksa
ksa

Reputation: 403

Yet another way:

select t.k
,(select listagg(column_value,', ') within group (order by column_value)
  from table(v1)) v1 
,(select listagg(column_value,', ') within group (order by column_value)
  from table(v2)) v2 
from (select k
            ,cast(collect (distinct v1) as sys.odcinumberlist) v1
            ,cast(collect (distinct v2) as sys.odcinumberlist) v2
      from T t
      group by k) t

SQL Fiddle

or without cast to odcinumberlist:

 select t.k
    ,(select listagg(column_value,', ') within group (order by column_value)
      from table(v1)) v1 
    ,(select listagg(column_value,', ') within group (order by column_value)
      from table(v2)) v2 
    from (select k
                ,collect (distinct v1) v1
                ,collect (distinct v2) v2
          from T t
          group by k) t

Upvotes: 0

Lukas
Lukas

Reputation: 29

select K, listagg(case when chgv1 = 1 then v1 else null end,',') within group (order by v1) as v1lst, --Only consider cases in listagg when rows have changed
          listagg(case when chgv2 = 1 then v2 else null end,',') within group (order by v2) as v2lst  --Only consider cases in listagg when rows have changed
from (
 select k, v1, v2,
        row_number() over (partition by k,v1 order by v1 ) as chgv1, --Detect changes in v1. In this case it's 1.
        row_number() over (partition by k,v2 order by v1 ) as chgv2  --Detect changes in v1. In this case it's 1.
 from t)
group by k;

Upvotes: 0

user3278460
user3278460

Reputation:

You can try undocumented function wm_concat

select k, wm_concat(distinct(v1)), wm_concat(distinct(v2))
from t
group by k

or with listagg

select k, listagg(v1, ', ') within group (order by k), listagg(v2, ', ') within group (order by k)
from (
    select distinct k, v1, null v2 from t  
    union all
    select distinct k, null v1, v2 from t
)
group by k

Upvotes: 1

Related Questions