Flatten in SQL Server

Posted by Joe Enos on August 18, 2021 · 1 min read
CREATE TYPE [dbo].[SingleColumnText] AS TABLE (
[TextValue] NVARCHAR (MAX) NULL);


create function dbo.Flatten
(
@input dbo.SingleColumnText readonly
,@delimiter nvarchar(max)
)
returns nvarchar(max)
as
begin
return (
select stuff((
select @delimiter + TextValue
from @input
for xml path(''), type
).value('(./text())[1]', 'nvarchar(max)'), 1, 1, '')
);
end;