Try this

WITH CTE
AS
(
    SELECT 1 AS ID, 1 AS NUM, 'STRING 1' AS STRING
    UNION ALL 
    SELECT 1, 2, 'STRING 2'
    UNION ALL 
    SELECT 1, 3, 'STRING 3'
    UNION ALL 
    SELECT 1, 4, 'STRING 4'
    UNION ALL 
    SELECT 2, 1, 'STRING 1'
    UNION ALL 
    SELECT 2, 2, 'STRING 2'
)
SELECT ID,
    SUBSTRING((SELECT 
        ', ' + string AS 'text()'
     FROM cte c2 
     WHERE c2.id = c1.id
     FOR XML PATH('')),2,8000) AS string
FROM cte c1
GROUP BY id

/*
ID          string
----------- ----------------------------------------
1            STRING 1, STRING 2, STRING 3, STRING 4
2            STRING 1, STRING 2
*/

The approach used in this query is explained here

Jacob, It is better to make use of STUFF function to remove the first comma In your method you are using SUBSTRING and hardcoding the value 8000 It is better you use this way so that you dont need to worry about the full length of the output

WITH CTE
AS
(
    SELECT 1 AS ID, 1 AS NUM, 'STRING 1' AS STRING
    UNION ALL 
    SELECT 1, 2, 'STRING 2'
    UNION ALL 
    SELECT 1, 3, 'STRING 3'
    UNION ALL 
    SELECT 1, 4, 'STRING 4'
    UNION ALL 
    SELECT 2, 1, 'STRING 1'
    UNION ALL 
    SELECT 2, 2, 'STRING 2'
)

SELECT ID,
    STUFF((SELECT 
        ', ' + string AS 'text()'
     FROM cte c2 
     WHERE c2.id = c1.id
     FOR XML PATH('')),1,1,'') AS string
FROM cte c1
GROUP BY id

Agreed

I would suggest this solution

/*

IF  EXISTS(SELECT *

             FROM sys.objects

            WHERE object_id = OBJECT_ID(N'dbo.ufn_GetConcatString')

              AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

   DROP FUNCTION dbo.ufn_GetConcatString;

GO 

CREATE FUNCTION ufn_GetConcatString(@id INT)

RETURNS VARCHAR(MAX)

AS

BEGIN

   DECLARE @concat_string AS VARCHAR(MAX) = '';

   WITH CTE AS

   (

      SELECT 1 AS ID, 1 AS NUM, 'STRING 1' AS STRING

       UNION ALL 

      SELECT 1, 2, 'STRING 2'

       UNION ALL 

      SELECT 1, 3, 'STRING 3'

       UNION ALL 

      SELECT 1, 4, 'STRING 4'

       UNION ALL 

      SELECT 2, 1, 'STRING 1'

       UNION ALL 

      SELECT 2, 2, 'STRING 2'

   )

   SELECT @concat_string = CASE WHEN @concat_string <> ''

                                     THEN @concat_string + ', '

                                ELSE @concat_string

                           END + STRING

     FROM CTE

    WHERE ID = @id;

   RETURN @concat_string;

END

GO

*/

;WITH CTE AS

(

   SELECT 1 AS ID, 1 AS NUM, 'STRING 1' AS STRING

    UNION ALL 

   SELECT 1, 2, 'STRING 2'

    UNION ALL 

   SELECT 1, 3, 'STRING 3'

    UNION ALL 

   SELECT 1, 4, 'STRING 4'

    UNION ALL 

   SELECT 2, 1, 'STRING 1'

    UNION ALL 

   SELECT 2, 2, 'STRING 2'

)

SELECT T.ID, dbo.ufn_GetConcatString(T.ID) AS String

  FROM (SELECT DISTINCT ID

          FROM CTE) AS T

Is there a reason you prefer UDF over for XML?

I do not prefer UDF over FOR XML, but I prefer the method of assigning multiple values to the same variable all at once (which I found to be less expensive than FOR XML).

Ok. You may need to run that against large set of data and post the execution time result