## Rows to String

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
```

#### Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

• [url=http://www.sqlsnippets.com/en/topic-12286.html]SQL Snippets: Rows to String - LEAD Method[/url]