Converting Column Values as Comma Separated String based on condition in SQL

Requirement- Our Requirement was Employee which are working on same Projects OR Employee who are assigned to same projects should come as a comma separated string in a single row for each Project from Project table  like below..

Output Required

ProjectName           name

Project1                rakesh, mukesh, sukesh

Project2                ajit, surjit

Below is test Project  table structure.

create table Projects

(Projectname varchar(200),Empname varchar(200)

)

insert into Projects values ('Project1','rakesh'),('Project1','mukesh'),('Project1','sukesh'),('Project2','ajit'),('Project2','surjit')

select * from Projects

Projectname      Empname

Project1            rakesh

Project1            mukesh

Project1            sukesh

Project2            ajit

Project2            surjit

In the above Projects table

For Project1-Employee assigned-Rakesh,  Mukesh,  Sukesh

For Project2 –Employee assigned-Ajit, Surjit

Each Row in Project table is showing Project Name and employee name assigned to that project.

We want the output like all employee names assigned to same project  should come as comma separated value as an string in a single row for each project.

So below is the Script  that convert column values as comma separated values

--create table Projects

--(Projectname varchar(200),Empname varchar(200)

--)

--

--insert into Projects values ('Project1','rakesh'),('Project1','mukesh'),('Project1','sukesh'),('Project2','ajit'),('Project2','surjit')

--Below script will show column values as comma separated string based on condition

declare @ProjectName nvarchar(100)

declare @name nvarchar(200)

declare @newProjectName nvarchar(100)

declare @newname nvarchar(100)

set @newProjectName='0'

set @newname ='0'

create table #tbl (ProjectName varchar(200) ,name varchar(100))

declare C cursor  for select Projectname,Empname from Projects

open  c

fetch next from c into @ProjectName,@name

set @newProjectName=@ProjectName

while @@FETCH_STATUS =0

begin

if @newProjectName= @ProjectName

begin

  if (@newname ='0')

 begin

   set @newname =@name

 end

   else

   begin

   set @newname =@newname  + ', '+ @name

end

end

if @newProjectName<>@ProjectName

begin

insert into #tbl values(@newProjectName,@newname )

set @newProjectName=@ProjectName

set @newname =@name

end

fetch next from c into @ProjectName,@name

end

if @newProjectName=@ProjectName

begin

insert into #tbl values(@newProjectName,@newname )

end

close c

deallocate c

select * from #tbl

drop Table #tbl

Script Output 

ProjectName                 Name

Project1                        rakesh, mukesh, sukesh

Project2                        ajit, surjit