Table valued function (TVF) on a comma-separated field

Question

I have this table:

create table Test (Value varchar(111))
insert Test select 'a,b,c'

I want to create a table valued function where I pass Test.Value and it returns the below table:

Where Value comes from Test table, and Item values are generated in this way: first item is whole value by itself (in example it consists of three comma-separated values), once there aren't any three comma-separated values, we go from left to right for two comma-separated values.

We go strictly from left to right, so there isn't any need for items like a,c or b,a. And then we go finally to one comma-separated value, which is a, b and c.

ItemLayer is just a layer which is being processed. Obviously, the separator should be a comma, and tvf should return Item and ItemLayer. I think the query should look something like this:

SELECT *
FROM Test t
CROSS JOIN fn_getItemsFromValues(t.Value) f

I think there should be some sort of a recursive CTE, but I can't figure it out how.

Here is an output if Value was 'a,b,c,d':

I'm using SQL Server 2017. I've tried this, but I'm stuck. Something is very off here.

DECLARE @data VARCHAR(100) = 'a,b,c'
;WITH CTE AS
(
    SELECT @data TXT, LEFT(@data,1) Col1
    UNION ALL
    SELECT STUFF(TXT,1,1,'') TXT, LEFT(TXT,1) Col1 FROM CTE
    WHERE LEN(TXT) > 0
)
select Col1,txt from CTE

Answer 1

I'm not sure why a,c is not in the list. But you can generate all combinations by splitting the string and then using a recursive CTE:

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;

Here is a db<>fiddle.

EDIT:

I think the best approach to the "adjacent" limitation is really a tweak on the previous solution. This adds a locator for the element and just allows the next element to be brought in on the recursive step:

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;

This doesn't work if you have duplicate elements. If that is the case, ask a new question.