SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table S(
Department nvarchar(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


WITH CTE01 AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY flag ORDER BY Department) AS NO
FROM S)

SELECT *
FROM CTE01


Department
flag
NO


A
0
1

B
0
2

E
0
3

F
0
4

H
0
5

C
1
1

D
1
2

G
1
3

I
1
4


WITH CTE01 AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY flag ORDER BY Department) AS NO
FROM S),
  
CTE02 AS (
SELECT flag,Department,NO
  ,cast(Department as nvarchar(100)) as AllStr,1 as ID
FROM CTE01
UNION ALL
SELECT NS.flag,NS.Department,NS.NO
  ,cast(CTE02.AllStr +','+ NS.Department as nvarchar(100)) as AllStr
  ,ID+1 AS ID
FROM CTE01 AS NS
INNER JOIN CTE02 ON NS.flag = CTE02.flag
AND NS.NO - 1 = CTE02.NO
)

SELECT A.flag,A.AllStr
FROM CTE02 AS A
INNER JOIN (  
SELECT flag,MAX(ID) AS MAXID
FROM CTE02
GROUP BY flag) AS B ON B.flag=A.flag AND B.MAXID = A.ID
order by flag


flag
AllStr


0
A,B,E,F,H

1
C,D,G,I