This tutorial explains how the Rows to String solution presented in Hierarchical Method works. Note: If you are unfamiliar with Oracle's hierarchical features you should review the tutorials at SQL Features Tutorials: Hierarchical Data tutorial before proceeding.
The basic solution in the preceding tutorial looked like this.
set null "(null)"
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 understand how this query works we will consider its individual components. First, look at the results of the FROM clause subquery.
break on group_key skip 1
select
group_key ,
val ,
row_number() over
( partition by group_key
order by val
)
as val_index
from
t
;
GROUP_KEY VAL VAL_INDEX
---------- ---------- ----------
Group 1 a 1
Group 2 a 1
b 2
Group 3 a 1
b 2
c 3
Group 4 a 1
a 2
b 3
b 4
Group 5 a 1
b 2
d 3
e 4
(null) 5
Group 6 (null) 1
Group 7 (null) 1
(null) 2
Group 8 a 1
a 2
b 3
b 4
(null) 5
(null) 6
(null) (null) 1
VAL_INDEX gives us a column we can use to hierarchically connect these rows, like this.
select
tb.group_key ,
tb.val ,
tb.val_index ,
SYS_CONNECT_BY_PATH( tb.val, ',' ) connect_by_path
from
( select
group_key ,
val ,
row_number() over
( partition by group_key
order by val
)
as val_index
from
t
) tb
CONNECT BY
VAL_INDEX = PRIOR VAL_INDEX + 1 AND
GROUP_KEY = PRIOR GROUP_KEY
START WITH
VAL_INDEX = 1
;
GROUP_KEY VAL VAL_INDEX CONNECT_BY_PATH
---------- ---------- ---------- ---------------
Group 1 a 1 ,a
Group 2 a 1 ,a
b 2 ,a,b
Group 3 a 1 ,a
b 2 ,a,b
c 3 ,a,b,c
Group 4 a 1 ,a
a 2 ,a,a
b 3 ,a,a,b
b 4 ,a,a,b,b
Group 5 a 1 ,a
b 2 ,a,b
d 3 ,a,b,d
e 4 ,a,b,d,e
(null) 5 ,a,b,d,e,
Group 6 (null) 1 ,
Group 7 (null) 1 ,
(null) 2 ,,
Group 8 a 1 ,a
a 2 ,a,a
b 3 ,a,a,b
b 4 ,a,a,b,b
(null) 5 ,a,a,b,b,
(null) 6 ,a,a,b,b,,
(null) (null) 1 ,
Once the rows are hierarchically connected we see that the CONNECT_BY_PATH values of the leaf rows are very close to the result we want. To identify these rows we can use the CONNECT_BY_ISLEAF function.
select
tb.group_key ,
tb.val ,
tb.val_index ,
sys_connect_by_path( tb.val, ',' ) connect_by_path ,
CONNECT_BY_ISLEAF
from
( select
group_key ,
val ,
row_number() over
( partition by group_key
order by val
)
as val_index
from
t
) tb
connect by
val_index = prior val_index + 1 and
group_key = prior group_key
start with
val_index = 1
;
GROUP_KEY VAL VAL_INDEX CONNECT_BY_PATH CONNECT_BY_ISLEAF
---------- ---------- ---------- --------------- -----------------
Group 1 a 1 ,a 1
Group 2 a 1 ,a 0
b 2 ,a,b 1
Group 3 a 1 ,a 0
b 2 ,a,b 0
c 3 ,a,b,c 1
Group 4 a 1 ,a 0
a 2 ,a,a 0
b 3 ,a,a,b 0
b 4 ,a,a,b,b 1
Group 5 a 1 ,a 0
b 2 ,a,b 0
d 3 ,a,b,d 0
e 4 ,a,b,d,e 0
(null) 5 ,a,b,d,e, 1
Group 6 (null) 1 , 1
Group 7 (null) 1 , 0
(null) 2 ,, 1
Group 8 a 1 ,a 0
a 2 ,a,a 0
b 3 ,a,a,b 0
b 4 ,a,a,b,b 0
(null) 5 ,a,a,b,b, 0
(null) 6 ,a,a,b,b,, 1
(null) (null) 1 , 1
Filtering out the non-leaf rows and stripping the leading "," in CONNECT_BY_PATH gives us our final solution.
select
tb.group_key ,
tb.val_index ,
SUBSTR -- strips the leading ","
( 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 -- selects only leaf rows
connect by
val_index = prior val_index + 1 and
group_key = prior group_key
start with
val_index = 1
;
GROUP_KEY VAL_INDEX STRING ---------- ---------- --------------- Group 1 1 a Group 2 2 a,b Group 3 3 a,b,c Group 4 4 a,a,b,b Group 5 5 a,b,d,e, Group 6 1 (null) Group 7 2 , Group 8 6 a,a,b,b,, (null) 1 (null)