SQL Server Flatten and Unflatten

Assuming you have a table type named SingleColumnText which contains (obviously) a single column of text, named [TextValue]:

create function util.Flatten
(
    @input util.SingleColumnText readonly
    ,@delimiter nvarchar(max)
)
returns nvarchar(max)
as
begin
    declare @result nvarchar(max);
    select @result = coalesce(@result + @delimiter, '') + TextValue
    from @input
    order by PK;
 
    return @result;
end;
go
create function util.Unflatten
(
    @input nvarchar(max)
    ,@delimiter nchar(1)
)
returns table
as
    return
    (
        select
            row_number() over (order by n) - 1 [Idx]
            ,substring(@input, n, charindex(@delimiter, @input + @delimiter, n) - n) [TextValue]
        from util.Numbers
        where n <= len(@input) 
        and substring(@delimiter + @input, n, 1) = @delimiter
    );
go