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
Oracle's hierarchical features will be used to achieve this result. Other tutorials in this section present alternate approaches. For tutorials on understanding Oracle's hierarchical features see SQL Features Tutorials: Hierarchical Data.
First, let's take a look at the 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)
Now here is the solution.
clear breaks
select
tb.group_key ,
substr
( sys_connect_by_path( tb.val, ',' ) ,
2
) as string
from
( select
group_key ,
val ,
row_number() over
( partition by group_key
order by val
)
as val_index
from
t
) tb
where
connect_by_isleaf = 1
connect by
val_index = prior val_index + 1 and
group_key = prior group_key
start with
val_index = 1
;
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() function.
select
tb.group_key ,
substr
( sys_connect_by_path( tb.val, ',' ) ,
2
) as string
from
( select
group_key ,
val ,
row_number() over
( partition by group_key
ORDER BY VAL DESC -- sort in descending order this time
)
as val_index
from
t
) tb
where
connect_by_isleaf = 1
connect by
val_index = prior val_index + 1 and
group_key = prior group_key
start with
val_index = 1
;
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 out duplicate VAL values, like those in Group 4, use a subquery as indicated below.
select
tb.group_key ,
substr
( sys_connect_by_path( tb.val, ',' ) ,
2
) as string
from
( select
group_key ,
val ,
row_number() over
( partition by group_key
order by val
)
as val_index
from
( SELECT DISTINCT GROUP_KEY, VAL FROM T ) tc -- provides distinct values
) tb
where
connect_by_isleaf = 1
connect by
val_index = prior val_index + 1 and
group_key = prior group_key
start with
val_index = 1
;
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 values out of your results, like the ones in Groups 5 and 6, include a WHERE clause in the FROM clause subquery.
select
tb.group_key ,
substr
( sys_connect_by_path( tb.val, ',' ) ,
2
) as string
from
( select
group_key ,
val ,
row_number() over
( partition by group_key
order by val
)
as val_index
from
t
WHERE
VAL IS NOT NULL -- filters out null VAL values
) tb
where
connect_by_isleaf = 1
connect by
val_index = prior val_index + 1 and
group_key = prior group_key
start with
val_index = 1
;
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
In the next topic, Hierarchical Method - Explained, we analyze this technique step by step.