SQL Server concatenation string [SQL Server]

-- Create a table

 use tempdb
IF OBJECT_ID('Tab') is not null
     DROP TABLE Tab
GO
CREATE TABLE Tab
(
      [Col1] INT
      ,[Col2] nvarchar(1)
)

-- Generate Test Data

 INSERT Tab
SELECT 1,N'a'
   UNION all
SELECT 1,N'b'
   UNION all
SELECT 1,N'c'
   UNION all
SELECT 2,N'd'
   UNION all
SELECT 2,N'e'
   UNION all
SELECT 3,N'f'
GO

Select col1, T. col2 from tab t

/******************** Result ****************** **

Col1 col2
1
1 B
1 c

2 D
2 E

3 F

/*************************************** ****/

-- Create a function

Code

 IF OBJECT_ID('F_Str') is not null
    DROP FUNCTION F_Str
go
CREATE FUNCTION F_Str
(
       @Col1 INT
)
RETURNS NVARCHAR(1000)
AS
  BEGIN
      DECLARE @Str NVARCHAR(1000)
      SELECT @Str=ISNULL(@Str+',','')+Col2 
             FROM Tab 
             WHERE Col1=@Col1
      RETURN @Str
  END
GO

-- Call

 SELECT Col1,Col2=dbo.F_Str(Col1)
    FROM Tab
GROUP BY Col1

-- Or

 SELECT DISTINCT Col1,Col2=dbo.F_Str(Col1)
   FROM Tab
GO

-- Running result

Col1 col2

1 a, B, c
2 D, E
3 F