Child Value Concatenation Problem 1

[Alundil]

I've scoured the internet looking for options on how to accomplish this...and I am stuck. Ready for some assistance

SQL Server 2000

I am trying to concatenate a list of allergies for a specific patient.

these are stored in table PatientAllergy
PA.ID - PA.PatientID - PA.AllergyID
1 1 1
2 1 2
3 2 1
4 1 3

The allergy names are stored in another table
AL.ID - AL.AllergyDesc
1 Bees
2 Latex
3 Penicillin

Then you have patients in another table
P.ID - P.Name
1 Smith
2 Doe


What I am trying to get is the following result:

P.ID - P.Name - Allergies
1 Smith Bees, Latex, Penicillin
2 Doe Bees


The plan is to fit this result, specifically the "Allergies" concatenated field, into another larger query as a sub query.


-- Andy

 

[gmmastros]

The normal response to a question like this is to use a "FOR XML PATH" trick to concatenate your strings. Since you are still using SQL2000, this trick will not work for you.

Instead, I recommend you create a user defined function where you pass the PatientId and returns a varchar(8000) string with the concatenated results. There should be enough information in the following thread for you to create the function (there is a full example). Once you have created the function, using it should be relatively simple.

thread183-1159740



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sds814]

How is the performance of using FOR XML PATH? Does it slow down performance?

 

[Alundil]

sds, I'd answer your question, but since I am still in the veritable stone age as far as SQL Server is concerned, I'll let others respond.

gmmastros - i'll be trying your suggestion shortly. thank you.


-- Andy

 

[Alundil]

gmmastros - thank you very much - that is just what the doctor ordered.

Worked like a charm.


-- Andy

 

[gmmastros]

sds814,

I haven't seen it first hand, but I'm told that the "FOR XML PATH" method is faster than using a user defined function.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom