Follow on question
This is a follow on question from
https://www.experts-exchange.com/questions/21759179/Create-one-row-of-data-from-a-column-of-data.html
Is there a way to make this work so that it only returns one instance of the JOB, for example ID 0 has JOB J3 twice and ID 1 has JOB J2 twice
ID JOB
0 J1
0 J2
0 J3
0 J3
1 J2
1 J2
1 J3
2 J4
2 J5
3 J3
and I would like to return a dataset in this format.
ID JOB
0 J1, J2, J3
1 J2, J3
2 J4, J5
3 J3
https://www.experts-exchange.com/questions/21759179/Create-one-row-of-data-from-a-column-of-data.html
Is there a way to make this work so that it only returns one instance of the JOB, for example ID 0 has JOB J3 twice and ID 1 has JOB J2 twice
ID JOB
0 J1
0 J2
0 J3
0 J3
1 J2
1 J2
1 J3
2 J4
2 J5
3 J3
and I would like to return a dataset in this format.
ID JOB
0 J1, J2, J3
1 J2, J3
2 J4, J5
3 J3
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just add a GROUP BY clause to your function, as in:
CREATE function dbo.CollectJobs (@id int)
returns varchar(8000)
as
BEGIN
declare @res varchar(8000)
select @res = coalesce ( @res + ',' , '' ) + JOB
From yourtable
where ID = @ID
Group By JOB
return @res
END
CREATE function dbo.CollectJobs (@id int)
returns varchar(8000)
as
BEGIN
declare @res varchar(8000)
select @res = coalesce ( @res + ',' , '' ) + JOB
From yourtable
where ID = @ID
Group By JOB
return @res
END
Oops, I should have refreshed.
ASKER
Great that has worked fine thanks.
ASKER
create function dbo.CollectJobs ( @id int )
returns varchar(8000)
as
begin
declare @res varchar(8000)
select @res = coalesce ( @res + ',' , '' ) + JOB
from yourtable where ID = @ID
return (@res )
end
and use this function:
select id, dbo.CollectJobs ( id) as Jobs
from yourtable
group by id