Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Question Unanswered: Stored Procedure-Delimited Field Listing

    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.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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:

    Code:
    INSERT into @T 
    select id, Description 
    from AllergyGrps
    where id =  @array_value
    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.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    SELECT PatItems.PatientID, AllergyGrps.Description
       FROM PatItems
       CROSS APPLY dbo.fnSplit(patItems.refMemo) AS s
       INNER JOIN AllergyGrps
          ON (AllergyGrps.id = split.col1)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Stored Procedure-Delimited Field Listing

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

    Code:
    select a.PatientID ,g.ID ,g.Description
    from
    (select PatientID ,cast('<list><a>'+replace(convert(nvarchar(max),RefMemo),':','</a><a>')+'</a></list>' as xml) as Allergy from PatItems ) a
    cross apply a.Allergy.nodes('/list/a') Y(X)
    inner join AllergyGrps g
    on g.ID = x.value('.[1]','int')

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Nov 2011
    Posts
    2

    Thanks for the Replys

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

    Code:
    CREATE VIEW view_Allergy_List AS
    select a.PatientID ,g.ID ,g.Description
    from
    (select PatientID ,cast('<list><a>'+replace(convert(nvarchar(max),RefMemo),':','</a><a>')+'</a></list>' as xml) as Allergy from view_Allergy_Patient_Items ) a
    cross apply a.Allergy.nodes('/list/a') Y(X)
    inner join AllergyGrps g
    on g.ID = x.value('.[1]','int')
    AND

    Code:
    CREATE VIEW view_Allergy_Delimited AS
    SELECT T1.PatientID  
               ,Allergies = SubString (( SELECT ', ' + T2.Description 
    FROM view_Allergy_List as T2 
    WHERE T1.PatientID = T2.PatientID
    FOR XML PATH ( '' ) ), 3, 1000) 
    FROM view_Allergy_List as T1 
    GROUP BY PatientID
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •