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