Trouble combining rows into one column using CAST(

Ok SO, here's your time to shine!

No really, I'm getting my butt kicked by an MS-SQL query that I can't seem to get to work.

What I am trying to do is search on a patient name; but also return patients who have a similar first or last name to the querying patient's last name. So "John Smith" can return anyone named "John Smith" or anyone who has a first or last name like "smith". If the a patient has multiple disease states, then combine those disease states into a single column. I have the following tables (though of course there are many more columns, but these are the most imortant):

Patient Table
PatientID    FirstName    LastName    UserIDFK
10000        John         Smith       1
10001        Miss         Smith       2
10002        Smith        Bomb        3
10003        Bobby        Smith       4
-- etc

DiseaseStateForUser
UserIDFK    DiseaseStateRefId
1           1
1           2
2           2
3           1
3           2
4           1

GlobalLookUp
RefId    Ref_Code
1        HIV
2        HEPC

The results I'm looking for are this:

PatientID    FirstName    LastName    DiseaseStates
10000        John         Smith       HIV|HEPC
10001        Miss         Smith       HEPC
10002        Smith        Bomb        HIV|HEPC
10003        Bobby        Smith       HIV

I've taken the examples from these questions (and countless others):

• Is there a way to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field?
• Simulating group_concat MySQL function in MS SQL Server 2005?

As well as from this blog post Emulating MySQL’s GROUP_CONCAT() Function in SQL Server 2005 I came up with the following SQL procedure

DECLARE 
    @PatientID          INT=null,           
    @FirstName          Varchar(15)= null,
    @LastName           Varchar(15)= 'Smith',
    @Name         开发者_如何学运维  Varchar(15) = 'John Smith',

Select
    Patient.First_Name,
    Patient.Last_Name, 
    patient.PatientID,      
    (select CAST(GlobalLookUp.Ref_Code + '|' as VARCHAR(MAX))
     from 
        TBL_PATIENT patient
        ,TBL_GBLLOOKUP GlobalLookUp
        ,TBL_DiseaseStateForUser DiseaseStateForUser
        -- Try and make a collection of all the PatientIDs
        -- that match the search criteria 
        -- so that only these are used to build
        -- the DiseaseStatesColumn
        ,(Select
            Patient.PatientID
                FROM TBL_PATIENT patient
                    ,TBL_SITEMASTER SiteMaster 
                    ,TBL_USERMASTER UserMaster
                    ,TBL_USERSINSITES UserInSites
                    ,TBL_GBLLOOKUP GlobalLookUp
                    ,TBL_DiseaseStateForUser DiseaseStateForUser
                WHERE   (((patient.[Last_Name] like @LastName + '%') OR (patient.[Last_Name] Like @Name + '%' ))
                            OR ((patient.[First_Name] Like @Name + '%' ))
                            OR  (patient.[First_Name] + ' ' + patient.[Last_Name] Like @Name + '%' ))
                    AND     UserMaster.User_Id = UserInSites.User_Id_FK
                    AND     UserInSites.Site_Id_FK = SiteMaster.Site_Id
                    AND     UserInSites.Is_Active = 'True'
                    AND     patient.[User_Id_FK] = UserMaster.[User_Id] 
                    AND     (DiseaseStateForUser.User_Id_FK = patient.User_Id_FK
                    AND     DiseaseStateForUser.DiseaseState_RefId_FK = GlobalLookUp.Ref_Id)
                    and     DiseaseStateForUser.Is_Active='True'
                    AND     patient.[Is_Active] = 'TRUE'
            group by Patient.PatientID) as PATIENTIDs
    where patient.PatientID = PATIENTIDs.PatientID  
            AND     (DiseaseStateForUser.User_Id_FK = patient.User_Id_FK
            AND     DiseaseStateForUser.DiseaseState_RefId_FK = GlobalLookUp.Ref_Id)
    For XML PATH('')) as MultiDiseaseState

FROM TBL_PATIENT patient, TBL_SITEMASTER SiteMaster ,TBL_USERMASTER UserMaster,TBL_USERSINSITES UserInSites, TBL_GBLLOOKUP GlobalLookUp, TBL_DiseaseStateForUser DiseaseStateForUser
WHERE   (((patient.[Last_Name] like @LastName + '%') OR (patient.[Last_Name] Like @Name + '%' ))
            or ((patient.[First_Name] Like @Name + '%' ))
            OR  (patient.[First_Name] + ' ' + patient.[Last_Name] Like @Name + '%' ))
    AND     patient.PatientID = patient.PatientID
    AND         UserMaster.User_Id = UserInSites.User_Id_FK
    AND     UserInSites.Site_Id_FK = SiteMaster.Site_Id
    AND     UserInSites.Is_Active = 'True'
    AND     patient.[User_Id_FK] = UserMaster.[User_Id] 
    AND     DiseaseStateForUser.User_Id_FK = patient.User_Id_FK
    AND     DiseaseStateForUser.DiseaseState_RefId_FK = GlobalLookUp.Ref_Id
    and     DiseaseStateForUser.Is_Active='True'
    AND     patient.[Is_Active] = 'TRUE'
group by PatientID, patient.First_Name, patient.Last_Name, GlobalLookUp.Ref_Code
order by PatientID

Unfortunately, this query nets me the following:

PatientID    FirstName    LastName    MultiDiseaseState
10000        John         Smith       HIV|HEPC|HEPC|HIV|HEPC|HIV
10001        Miss         Smith       HIV|HEPC|HEPC|HIV|HEPC|HIV
10002        Smith        Bomb        HIV|HEPC|HEPC|HIV|HEPC|HIV
10003        Bobby        Smith       HIV|HEPC|HEPC|HIV|HEPC|HIV

In other words, the select CAST(GlobalLookUp.Ref_Code + '|' as VARCHAR(MAX)) call is building up the MultiDiseaseState column with all of the disease states for ALL of the selected patients.

I know there is something fundamentally wrong with the most inner SELECT statement, but I'm having a hard time figuring out what it is and how to write the query so that it builds only the disease states for a given patient.

Kind of a long post, but are there any suggestions people can make given the code snippets I've provided?

---

You should be able to use the Stuff function (I think it's only on SQL 2005 and higher) to make this work, I took your example data and wrote a demonstration off of that

    SET NOCOUNT ON

CREATE TABLE #Patient
(
PatientID INT,
FirstName varchar(25),
LastName varchar(25),
UserIDFK INT
)
INSERT INTO #PATIENT SELECT 10000,'John','Smith',1 
INSERT INTO #PATIENT SELECT 10001,'Miss','Smith',2 
INSERT INTO #PATIENT SELECT 10002,'Smith','Bomb',3 
INSERT INTO #PATIENT SELECT 10003,'Bobby','Smith',4 

CREATE TABLE #DiseaseStateForUser 
(
UserIDFK int,
DiseaseStateRefId int
)

INSERT INTO #DiseaseStateForUser SELECT 1,1 
INSERT INTO #DiseaseStateForUser SELECT 1,2 
INSERT INTO #DiseaseStateForUser SELECT 2,2 
INSERT INTO #DiseaseStateForUser SELECT 3,1 
INSERT INTO #DiseaseStateForUser SELECT 3,2 
INSERT INTO #DiseaseStateForUser SELECT 4,1 

CREATE TABLE #GlobalLookUp
(
RefId int,
Ref_Code varchar(10)
)
INSERT INTO #GlobalLookUp SELECT 1,'HIV'
INSERT INTO #GlobalLookUp SELECT 2,'HEPC'


SELECT 
    PatientID,
    UserIDFK,
    FirstName,
    LastName,
    STUFF(
    (SELECT '|' + l.Ref_Code 
    FROM #DiseaseStateForUser u with (Nolock) 
    JOIN dbo.#GlobalLookUp l with (nolock)
        ON u.DiseaseStateRefId = l.RefId
    WHERE u.UserIDFK = p.UserIDFK FOR XML PATH('')
    )
    , 1, 1, '')
FROM #PATIENT p with (Nolock)
GROUP BY PatientID, FirstName, LastName, UserIDFK