This tutorial shows how to collect values from multiple rows into a single, comma delimited string. This is also known as "string aggregation". It takes values like these

GROUP_KEY  VAL
---------- ----------
Group 3    a
Group 3    b
Group 3    c
 

and yields a string like this.

GROUP_KEY  STRING
---------- ---------------
Group 3    a,b,c
 

The solution to follow is a good choice when the maximum number of values is known and small. See other solutions in this section when this is not the case.

Test Data

desc t
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 GROUP_KEY                              VARCHAR2(10)
 VAL                                    VARCHAR2(10)

set null "(null)"

break on group_key skip 1 duplicates

select * from t order by group_key, val ;
 

GROUP_KEY  VAL
---------- ----------
Group 1    a

Group 2    a
Group 2    b

Group 3    a
Group 3    b
Group 3    c

Group 4    a
Group 4    a
Group 4    b
Group 4    b

Group 5    a
Group 5    b
Group 5    d
Group 5    e
Group 5    (null)

Group 6    (null)

Group 7    (null)
Group 7    (null)

Group 8    a
Group 8    a
Group 8    b
Group 8    b
Group 8    (null)
Group 8    (null)

(null)     (null)
 

Solution

In this solution, which uses the DECODE and MAX functions, we assume the total number of values ever collected will not exceed 6.

clear breaks

select
  group_key ,
  max( decode( val_number, 1 , val, null ) ) ||
  max( decode( val_number, 2 , ',' || val, null ) ) ||
  max( decode( val_number, 3 , ',' || val, null ) ) ||
  max( decode( val_number, 4 , ',' || val, null ) ) ||
  max( decode( val_number, 5 , ',' || val, null ) ) ||
  max( decode( val_number, 6 , ',' || val, null ) ) as string
from
  ( select
      group_key,
      row_number() over ( partition by group_key order by val ) as val_number ,
      val
    from t
  ) ta
group by group_key
order by group_key ;
 

GROUP_KEY  STRING
---------- ---------------
Group 1    a
Group 2    a,b
Group 3    a,b,c
Group 4    a,a,b,b
Group 5    a,b,d,e,
Group 6    (null)
Group 7    ,
Group 8    a,a,b,b,,
(null)     (null)
 

To control the order of the delimited values, change the ORDER BY clause in the ROW_NUMBER() argument.

select
  group_key ,
  max( decode( val_number, 1 , val, null ) ) ||
  max( decode( val_number, 2 , ',' || val, null ) ) ||
  max( decode( val_number, 3 , ',' || val, null ) ) ||
  max( decode( val_number, 4 , ',' || val, null ) ) ||
  max( decode( val_number, 5 , ',' || val, null ) ) ||
  max( decode( val_number, 6 , ',' || val, null ) ) as string
from
  ( select
      group_key,
      row_number() over ( partition by group_key ORDER BY VAL DESC ) as val_number ,
      val
    from t
  ) t_a
group by group_key
order by group_key ;
 

GROUP_KEY  STRING
---------- ---------------
Group 1    a
Group 2    b,a
Group 3    c,b,a
Group 4    b,b,a,a
Group 5    ,e,d,b,a
Group 6    (null)
Group 7    ,
Group 8    ,,b,b,a,a
(null)     (null)
 

To filter duplicate VAL values, like those in Groups 4, 7, and 8 from the end result we use an inline view in the FROM clause.

clear breaks

select
  group_key ,
  max( decode( val_number, 1 , val, null ) ) ||
  max( decode( val_number, 2 , ',' || val, null ) ) ||
  max( decode( val_number, 3 , ',' || val, null ) ) ||
  max( decode( val_number, 4 , ',' || val, null ) ) ||
  max( decode( val_number, 5 , ',' || val, null ) ) ||
  max( decode( val_number, 6 , ',' || val, null ) ) as string
from
  ( select
      group_key,
      row_number() over ( partition by group_key order by val ) as val_number ,
      val
    from ( SELECT DISTINCT GROUP_KEY, VAL FROM T ) T_A
  ) t_b
group by group_key
order by group_key ;
 

GROUP_KEY  STRING
---------- ---------------
Group 1    a
Group 2    a,b
Group 3    a,b,c
Group 4    a,b
Group 5    a,b,d,e,
Group 6    (null)
Group 7    (null)
Group 8    a,b,
(null)     (null)
 

To filter null VAL values, like the ones in Groups 5 and higher from the end result we can add a WHERE clause to the query.

select
  group_key ,
  max( decode( val_number, 1 , val, null ) ) ||
  max( decode( val_number, 2 , ',' || val, null ) ) ||
  max( decode( val_number, 3 , ',' || val, null ) ) ||
  max( decode( val_number, 4 , ',' || val, null ) ) ||
  max( decode( val_number, 5 , ',' || val, null ) ) ||
  max( decode( val_number, 6 , ',' || val, null ) ) as string
from
  ( select
      group_key,
      row_number() over ( partition by group_key order by val ) as val_number ,
      val
    from t
    WHERE VAL IS NOT NULL
  ) ta
group by group_key
order by group_key ;
 

GROUP_KEY  STRING
---------- ---------------
Group 1    a
Group 2    a,b
Group 3    a,b,c
Group 4    a,a,b,b
Group 5    a,b,d,e
Group 8    a,a,b,b
 

How It Works

The query basically uses techniques described in Rows to Columns: Aggregating Values and Rows to Columns: Transposing Many to One that pivot rows into columns. In the query above, instead of placing row values into individual columns we simply concatenate them together.