returning a list of records

[fikir]

I have a table called

emplyee

empId empname
1 John
2 Sean
3 Merry

each employee participate in one or more different projects

I am planning to get the list of projects the employee are taking part

I wrote a function fnProj which accepts an emplyee Id and returns all projects the employee is participating,

and I wrote this query to get all the projects

declare @projects varchar(2000)
set @projects = ''

select @projects = @projects + fnProj(empId) + ', '
from employee

select @projects

the query is returning the projects, but the problem is because many employees are participating in one project at the same time, that projects name is repeating many time
but I want the @projects to be a list of unique projects.
i.e the projects name appear ones even though many emplyees taking part.

the list should look like

proj1, proj2, proj3

Thanks

 

[sunila7]

Take a look at this thread. thread183-1159740

Sunil

 

[fikir]

Thank you sunil for your response, but that was not my question.

my question was how do I eliminate the repeating of project names from my list

as I wrote earlier, many employee work for the same project
so the query returns the same project name for each this employees, therefore the list contains repeated name of this project, I don't want that to happen
in other works, before a project name is concatinated to the list, the query should check whether that project name exists in the list, if not it concatinates otherwise, it leaves it

now I hope, you got my question

Thanks

 

[gmmastros]

fikir,

Is it possible to change the function so that it returns a distinct list of projects? Seems like that would be an easy fix to this problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fikir]

the function returns unique projects for a single employee,
but the problem comes when more than one employee is working in the same project
remeber the function accepts one employee ID at one time

the function returns the same project name for those employees working together in the same project, therefore my quesry returns a list of redundant project names

thanks

 

[sunila7]

Can you give us some sample data and expected results? and if you have written a function then that code will also be useful.

Sunil