Select Issue

[lah3233]

Hi!

I've got a SQL Statement that looks like this:
SELECT project.strprojectid, strprojecttitle, dtDueDate, strProjectStatus, strConsultantName
FROM project, projectConsultant, Consultant
WHERE project.strProjectID = '9jjx1ujn4sa'
AND project.strProjectID = ProjectConsultant.strProjectID
AND projectconsultant.strConsultantID = consultant.strConsultantID

Basically there is more than one consultant per project...and what I would like to do is pull the first four fields and then somehow pull my consultants and list them together in a new temp field so I only have one row of data. Can anyone help me with this problem?

Thanks!
Lori

 

[nigelrivett]

Use a udf to concatenate the consultants and call it in the select statement.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[lah3233]

How do I go about doing that?

 

[nigelrivett]

something like


create function GetConsultants
(
strProjectID varchar(20)
)
returns varchar(1000)
as
begin
declare @s varchar(1000)
select @s = coalesce(@s + ',','') + strConsultantName
from ProjectConsultant
join consultant
on consultant.strConsultantID = projectconsultant.strConsultantID
where ProjectConsultant.strProjectID = @Project_id
return @s
end
go

SELECT project.strprojectid, strprojecttitle, dtDueDate, strProjectStatus, strConsultantNames = dbo.GetConsultants(strProjectID)
FROM project
WHERE project.strProjectID = '9jjx1ujn4sa'


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[lah3233]

Where do I declare @Project_ID?

 

[lah3233]

I have never worked with functions before and this is the errors I am getting back

Server: Msg 170, Level 15, State 1, Procedure GetConsultants, Line 3
Line 3: Incorrect syntax near 'strProjectID'.
Server: Msg 178, Level 15, State 1, Procedure GetConsultants, Line 16
A RETURN statement with a return value cannot be used in this context.
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.GetConsultants'.