CTE will not server the purpose, you will be getting MaxRecursion error because there is no particular hierarchy is available in your table.

following query is written with respect for your desired output:

create table #tblemp (Deptno int, Empname nvarchar(50))

insert into #tblemp

select 10,'Niladri' union all

select 20,'Jeeva' union all

select 30,'Deepak' union all

select 30,'Prantik' union all

select 20,'Arina'

;With cte

AS

(

Select Distinct Deptno

from #tblemp

)

Select e.Deptno , Stuff((select ', ' + Empname

from #tblemp EE

Where e.Deptno = ee.Deptno

for xml Path ('')),1,2,'')

from cte E

Drop table #tblemp

Hope it helps in case of any query do let us know.