I am working with a Food and Nutrition database that contains individuals meal choice items as well as allergy information. My issue is that the allergy information is held as a delimited list of ID numbers in table and the ID corresponds with values in another table. Below is my example:

Table:[PatItems] - Holds individuals menu choices
Field:{RefMemo} - ntext - Holds a delimited list of allergy information (ex. 36:48:67)

Table:[AllergyGrps] - Holds allergy information
Field:{ID} - int identitycol - Unique number corresponding to each allergy name.

dbo.PatItems.RefMemo = dbo.AllergyGrps.ID

This does not work due to the list being delimited. I have attempted functions to parse out the delimited list:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnSplit] (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (col1 varchar(50))
AS
BEGIN
--DECLARE @T Table (col1 varchar(50))
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
DECLARE @separator_position INT -- This is used to locate each separator character
DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value

SET @array = @array + @separator

-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT @array_value = LEFT(@array, @separator_position - 1)
-- This is where you process the values passed.
INSERT into @T VALUES (@array_value)
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
-- This replaces what we just processed with and empty string
SELECT @array = STUFF(@array, 1, @separator_position, '')
END
RETURN
END
------------------------------------------------------
DECLARE @txtvar

SELECT @txtvar = PtI.RefMemo
FROM dbo.PatItems AS PtI

The only problem with this works by:
SELECT * FROM dbo.fnSplit(@txtvar,':')

This does not allow the ability to loop through for each PatientID and return values. Do anyone know how to create the following:

PatItems.PatientID='101'
PatItems.RefMemo='36:48:67'

Return:
PatItems.PatientID='101'
AllergyGrps.Description='Peanuts,Strawberry,Egg'

OR

PatItems.PatientID='101'
AllergyGrps.Description='Peanuts'
PatItems.PatientID='101'
AllergyGrps.Description='Strawberry'
PatItems.PatientID='101'
AllergyGrps.Description='Egg'


Any help is greatly appreciated.

If this is a small implementation, and most of the hits to this function are going to be single lookups, you could just add a call to the AllergyGrps table in the insert statement:

For large implementations, or heavy usage, this becomes a bit of a performance burden, and the design of the database should be changed to split off the patient allergies to their own mapping table.

If "small" is zero or one, and "large" is two or more, then I agree with MCrowley.

Otherwise, you can use dbo.fnSplit() easily (I know, I wrote it!) like:-PatP

Assuming SS2005 and above, here's yet another query for listing each allergy by PatientID...

Homerow,

I ran your script and it delivers perfect results, but I have no clue how or why this works.

This is not your first post that confronts me with the knowledge gap I have about XML syntax in T-SQL. Can you give me some books or URL's where I can learn about this? I tried BOL before, but I found the explanation hard to follow for someone new to this stuff.

The XML approach was the most seamless for me to integrate. I used:

AND

I was able to split the values, find their match in the AllergyGrps table, then delimit the list again. This created the ability to make a header on the report, list the allergies once, and then all of the menu items below for each individual. Thank you again for all of the assistance and replies.