how to use COALESCE in oracle to combine data from two rows

Question

I have the following query which is inside a loop and is breaking

SELECT   COALESCE(v_user_grade || ', ', '') || user_grade
          INTO v_user_grade
          FROM EXPIRATION_HISTORY__2
           WHERE hist_id = v_test;

So basically in each iteration of the loop we get a new value for v_test. In some cases this query would return multiple rows. that is when it is breaking.

What I want is:

Data in EXPIRATION_HISTORY__2:

hist_id    user_grade
301  ADMIN
302  ADMIN
302  USER

so now based on the above data. ...in the end of the iteration where v_test = 302. I would want v_user_grade to contain (ADMIN, USER)

Answer 1

I think the following link describes exactly that what you want:

http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

String Aggregation Techniques

On occasion it is necessary to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value.

I'm not an Oracle expert; but there are multiple solutions, depending on your DB enviroment.