SQL Snippets ™: Tutorials for Oracle Developers

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 LEAD analytic function, we assume the total number of values ever collected will not exceed 6.

clear breaks

select group_key, string
from
  (
    select
      group_key ,
      row_number() over ( partition by group_key order by val ) as position ,
      val ||
      lead( ','||val, 1 ) over ( partition by group_key order by val ) ||
      lead( ','||val, 2 ) over ( partition by group_key order by val ) ||
      lead( ','||val, 3 ) over ( partition by group_key order by val ) ||
      lead( ','||val, 4 ) over ( partition by group_key order by val ) ||
      lead( ','||val, 5 ) over ( partition by group_key order by val )
      as string
    from t
  )
where position = 1
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 clauses.

select group_key, string
from
  (
    select
      group_key ,
      row_number() over ( partition by group_key ORDER BY VAL DESC) as position ,
      val ||
      lead( ','||val, 1 ) over ( partition by group_key ORDER BY VAL DESC) ||
      lead( ','||val, 2 ) over ( partition by group_key ORDER BY VAL DESC) ||
      lead( ','||val, 3 ) over ( partition by group_key ORDER BY VAL DESC) ||
      lead( ','||val, 4 ) over ( partition by group_key ORDER BY VAL DESC) ||
      lead( ','||val, 5 ) over ( partition by group_key ORDER BY VAL DESC)
      as string
    from t
  )
where position = 1
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, string
from
  (
    select
      group_key ,
      row_number() over ( partition by group_key order by val ) as position ,
      val ||
      lead( ','||val, 1 ) over ( partition by group_key order by val ) ||
      lead( ','||val, 2 ) over ( partition by group_key order by val ) ||
      lead( ','||val, 3 ) over ( partition by group_key order by val ) ||
      lead( ','||val, 4 ) over ( partition by group_key order by val ) ||
      lead( ','||val, 5 ) over ( partition by group_key order by val )
      as string
    from ( SELECT DISTINCT GROUP_KEY, VAL FROM T ) T_A
  )
where position = 1
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, string
from
  (
    select
      group_key ,
      row_number() over ( partition by group_key order by val ) as position ,
      val ||
      lead( ','||val, 1 ) over ( partition by group_key order by val ) ||
      lead( ','||val, 2 ) over ( partition by group_key order by val ) ||
      lead( ','||val, 3 ) over ( partition by group_key order by val ) ||
      lead( ','||val, 4 ) over ( partition by group_key order by val ) ||
      lead( ','||val, 5 ) over ( partition by group_key order by val )
      as string
    from t
    WHERE VAL IS NOT NULL
  )
where position = 1
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