Flatten in SQL Server

CREATE TYPE [dbo].[SingleColumnText] AS TABLE (
    [TextValue] NVARCHAR (MAX) NULL);
GO

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;
GO

Leave a Reply

Your email address will not be published.