I have a SQL report that includes the patients allergies.  It is listing them in separate rows:

PtName           Room           AllergyName
DOE, JOHN      C228-98       Morphine
DOE, JOHN      C228-98       Penicillin G

I would like it to read:
DOE, JOHN      C228-98       Morphine, Penicillin G

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
There are multiple ways of approaching this problem. For example, a UDF that will return the concatenated list;

CREATE FUNCTION dbo.udfPatientAllergyList(@patientID INT)
RETURNS varchar(512)
	DECLARE @list varchar(512)

	SELECT @list = ISNULL(@list + ', ', '') + a.allergyDescription
	FROM patientAllergies AS pa
	INNER JOIN allergies AS a
		ON pa.AllergyID = a.AllergyID
	WHERE pa.patientID = @patientID

	SET @list = ISNULL(@list, '')
	RETURN @list

    dbo.udfPatientAllergyList(p.PatientID) as Allergies
FROM Patients as P

Open in new window

Of course, this results in quite a number of queries being run, as that SELECT is run for each patient on your report. But you get your lists.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kvrogersAuthor Commented:
I am unfamiliar with a UDF and do not know where to put in current report or is this an addition to the report called @list.  

kvrogersAuthor Commented:
I see now that this is a function that I have to create.  What type of function is it.  I have Table-valued and Scalar-valued functions.  Here is the current report attached,

kvrogersAuthor Commented:
Could you better explain the UDF?  I am unfamiliar and want to learn how it applies to my report so I included it above.  This way, if needed, I can recreate it in another report.

Thank you for your time

kvrogersAuthor Commented:
Does anyone have any ideas on how I can accomplish this?

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.