Oracle group and concatenate data in query

user_5908200 0
Viewed 20

My query return:

   NAME      DESC    PERCENT
   audi      yelow   10%
   audi      blue    20%
   audi      red     30%
   service   tires   null
   service2  tires   null

is it possible get this resault:

   NAME      DESC             PERCENT
   audi      yelow,blue,red   60%
   service   tires            null
   service2  tires            null
oracle sql
2 Answers

This is aggregation:

select name, listagg(desc, ',') within group (order by percent) as descriptions,
       sum(percent) as percent
from t
group by name;

Note: desc is a bad name for a column, because it is a SQL keyword (used with ORDER BY).

user_11440350

You could use LISTAGG and SUM to aggregate the rows:

with data as(
  select 'audi' a, 'yelow' b, 10 c from dual union all
  select 'audi', 'blue', 20 from dual union all
  select 'audi', 'red', 30 from dual union all
  select 'service', 'tires', null from dual union all
  select 'service2', 'tires', null from dual
)
SELECT a, 
       LISTAGG(b, ',') WITHIN GROUP (ORDER BY c)  vals, 
       SUM(c)  percentage
FROM   data
GROUP BY a
ORDER BY a;

A          VALS                 PERCENTAGE
---------- -------------------- ----------
audi       yelow,blue,red               60
service    tires                          
service2   tires
user_39896080
Related