Aggregate function to sum values from multiple rows into one result row

Question

it's been some years since I used the Oracle SQL pivot function and I cannot quite get mine to work for this short example.

I would like to transform the multiple columns/ rows from this table

ID   CLASS   NAME   WHEIGHT
1    2020    Tom    80
2    2020    Tim    100
3    2020    Ben    120

into this single result row:

CLASS    LISTAGG_NAMES    SUM_WHEIGHT
2020     Tom,Tim,Ben      300

Thank you very much for some help!

Gordon Linoff · Accepted Answer · 2020-05-06 14:11:15Z

1

This is not a "pivot" problem. You do not want separate columns for each value. You just want to concatenate them. Use listagg():

select class, listagg(name, ',') within group (order by weight) as names, sum(weight)
from t
group by class;

answered May 6, 2020 at 14:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you Gordon, that did the trick. I also changed the question title