Using FOR XML PATH

 https://www.sqlmatters.com/Articles/Converting%20row%20values%20in%20a%20table%20to%20a%20single%20concatenated%20string.aspx

Solution 5 : Using FOR XML PATH

Some of the XML statements introduced in SQL Server 2005 had to implement a means of looping around data in order to produce XML. This solution takes advantage of this, but strips out the XML specific parts to produce the comma separated list.

SELECT STUFF((SELECT ',' + Txt
            FROM ConcatenationDemo
            FOR XML PATH('')) ,1,1,'') AS Txt

sql server separar cadenas

CREATE FUNCTION [dbo].[SepararCadena](@cadena      NVARCHAR(MAX),  

                                     @delimitador CHAR(1))  

RETURNS @output TABLE(splitData NVARCHAR(MAX))  

AS  

     BEGIN  

         DECLARE @start INT  

         DECLARE @end INT  

         SELECT @start = 1,  

                @end = CHARINDEX(@delimitador, @cadena)  

         WHILE @start < LEN(@cadena) + 1  

             BEGIN  

                 IF @end = 0  

                     SET @end = LEN(@cadena) + 1  

                 INSERT INTO @output(splitData)  

                 VALUES(SUBSTRING(@cadena, @start, @end-@start))  

                 SET @start = @end + 1  

                 SET @end = CHARINDEX(@delimitador, @cadena, @start)  

             END  

         RETURN  

     END