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

user_1144035 · Answer

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.

user_14709607 · Answer

Here's what I got:

drop function if exists fn_getItemsFromValues 
go
create function fn_getItemsFromValues (@val varchar(100))
returns @ret table
(
[value] varchar (100)
,item varchar (100)
,ItemLayer int
) 
as
begin
    declare
        @v varchar (1000)=@val
        ,@i int=1
        ,@J int=1
        ,@v_n varchar (1000)=''

    set @v=@val
    declare
        @t table (val varchar (1000))


    while @i<=len(@v)
    begin
        set @j=1
        while @i+@J-1<=len(@v)
        begin
            set @v_n=substring (@v, @i, @j)
            if len(@v_n)>1
                set @v_n=isnull(nullif(LEFT(@v_n,1),',') ,'') +substring (@v_n,2, len(@v_n)-2)+isnull(nullif(right(@v_n,1),',') ,'')

            insert into @t
            select @v_n where @v_n!=','
            set @j=@j+1
        end
    set @i=@i+1
    end

    insert into @ret
    select @v, val, count(ln) over (partition by ln) 
    from (
        select distinct val, len(val) ln from @t
        )t
    return
end

--select * from  dbo.fn_getItemsFromValues ('a,b,c,d') order by 3

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

2 Answers