Rows to String

MAX DECODE Method

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.