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
