SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Create Table S(
Department nchar(1),
flag int 
)
INSERT INTO S
Values(N'A' , 0)
,(N'B' , 0)
,(N'C' , 1)
,(N'D' , 1)
,(N'E' , 0)
,(N'F' , 0)
,(N'G' , 1)
,(N'H' , 0)
,(N'I' , 1)
9 rows affected

select * from S


Department
flag


A
0

B
0

C
1

D
1

E
0

F
0

G
1

H
0

I
1


create FUNCTION [CombineRowString_01]
(@flag nvarchar(max)) 
returns nvarchar(max)  
begin  
    declare @str nvarchar(max)   
    set @str = '' 

    select @str = @str + TA.Department + ','     
    FROM S AS TA
    where 1=1
    AND	TA.flag = @flag

    set @str = ',' + replace(cast(@str AS nvarchar(max)),' ','')
    return @str
end  
SELECT DISTINCT 1 as flag,
replace(STUFF(
(
left(dbo.CombineRowString_01(1),len(dbo.CombineRowString_01(1))-1 )
),1,1,''),',',' , ') as 'Department'
FROM S AS A


flag
Department


1
C , D , G , I