SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Test (Value varchar(111))
insert Test select 'a,b,c'
1 rows affected

select * from test


Value


a,b,c


with t as (
      select t.value, convert(varchar(max), s.value) as val
      from test t cross apply
           string_split(t.value, ',') s
     ),
     cte as (
      select t.value, t.val as str, t.val as lastval, 1 as lev
      from t
      union all
      select cte.value, concat(cte.str, ',', t.val), t.val, lev + 1
      from cte join
           t
           on cte.value = t.value and cte.lastval < t.val
     )
select cte.*, dense_rank() over (order by lev desc) as itemlayer
from cte


value
str
lastval
lev
itemlayer


a,b,c
a,b,c
c
3
1

a,b,c
b,c
c
2
2

a,b,c
a,b
b
2
2

a,b,c
a,c
c
2
2

a,b,c
a
a
1
3

a,b,c
b
b
1
3

a,b,c
c
c
1
3


with cte as (
      select t.value, 1 as len, 1 as offset, 5 as max
      from test t
      union all
      select cte.value, len + 2, offset, max
      from cte
      where len + offset <= max 
      union all
      select cte.value, len, offset + 2, max
      from cte
      where offset + len <= max 
    )
select distinct substring(value, offset, len), dense_rank() over (order by len desc), offset, len
from cte
order by len desc


(No column name)
(No column name)
offset
len


a,b,c
1
1
5

a,b
2
1
3

b,c
2
3
3

a
3
1
1

b
3
3
1

c
3
5
1


with t as (
      select t.value, convert(varchar(max), s.value) as val,
             row_number() over (order by charindex(',' + s.value + ',', ',' + t.value + ',')) as ind
      from test t cross apply
           string_split(t.value, ',') s
     ),
     cte as (
      select t.value, t.val as str, t.ind, 1 as lev
      from t
      union all
      select cte.value, concat(cte.str, ',', t.val), t.ind, lev + 1
      from cte join
           t
           on cte.value = t.value and t.ind = cte.ind + 1
     )
 select cte.*, dense_rank() over (order by lev desc) as itemlayer
from cte


value
str
ind
lev
itemlayer


a,b,c
a,b,c
3
3
1

a,b,c
b,c
3
2
2

a,b,c
a,b
2
2
2

a,b,c
a
1
1
3

a,b,c
b
2
1
3

a,b,c
c
3
1
3
value	str	lastval	lev	itemlayer
a,b,c	a,b,c	c	3	1
a,b,c	b,c	c	2	2
a,b,c	a,b	b	2	2
a,b,c	a,c	c	2	2
a,b,c	a	a	1	3
a,b,c	b	b	1	3
a,b,c	c	c	1	3