Concatenation in SELECT and SPLIT functions with Transact SQL

Scalar UDF with variable concatenation in SELECT 

CREATE FUNCTION dbo.udf_select_concat ( @c INT )
    RETURNS VARCHAR(MAX) AS BEGIN
    DECLARE @p VARCHAR(MAX) ;
           SET @p = '' ;
        SELECT @p = @p + ProductName + ','
          FROM Products
         WHERE CategoryId = @c ;
    RETURN @p
    END

And, as for its usage:

    SELECT CategoryId, dbo.udf_select_concat( CategoryId )
      FROM Products
     GROUP BY CategoryId ;

SPLIT Function

CREATE FUNCTION [dbo].[Split]
(
@RowData NVARCHAR(MAX),
@Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE
(
ID INT IDENTITY(1,1),
Data NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @Iterator INT
SET @Iterator = 1
DECLARE @FoundIndex INT
SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)
WHILE (@FoundIndex>0)
BEGIN
INSERT INTO @RtnValue (data)
SELECT
Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))
SET @RowData = SUBSTRING(@RowData,
@FoundIndex + DATALENGTH(@Delimeter) / 2,
LEN(@RowData))
SET @Iterator = @Iterator + 1
SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
END

INSERT INTO @RtnValue (Data)
SELECT Data = LTRIM(RTRIM(@RowData))
RETURN
END

GO

Sources : http://balavardhanreddy.over-blog.com/15-categorie-10814096.html
               http://www.projectdmx.com/tsql/rowconcatenate.aspx