SQL Server Flatten and Unflatten

Posted by Joe Enos on June 23, 2021 · 2 mins read

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