Conversion failed when converting the nvarchar value 'value' to int I have the following code to pull a number of values from several rows into a single column and pipe-delimit the values.

SELECT DISTINCT P2.PatientID, 
SUBSTRING(
    (
        SELECT '| '+ CAST(P1.ProcedureCode AS NVARCHAR(MAX))  AS [text()]
        FROM #Proc50 P1
        WHERE P1.PatientID = P2.PatientID
        ORDER BY P1.PatientID
        FOR XML PATH ('')
    ), 2, 1000) [ProcedureCodes]
INTO #Proc_Concat
FROM #Proc50 P2


I then have a dataset that looks like this:

PatientId   ProcedureCodes

1111111      11100| 17271| 1CHKS| 1CHN1| 1CHNS| 36415| 51798| 69210| 70450| 70544

When I try to insert the values into the final table I receive the conversion error.  I tried casting the value but that hasn't helped.  I also created a table for insertion where the field was nvarchar.  

I am using SQL Server 2018.  Any help would be appreciated.

EDIT to answer questions:
Here is the definition of #Proc50


Both P1.PatientId and P2.PatientId are int

For the code that I am using to insert the results of the pipe-delimited procedure, I am creating a table for the insertion.

    CREATE TABLE dbo.tmpWellMedTest (
                    PatientId int not null
                    ,Lastname  varchar(30) not null
                    ,Firstname  varchar(30) not null
                    ,ProcedureCodes nvarchar(MAX) null
                    ,Diagnosis1 varchar(10) null
                    ,Diagnosis2 varchar(10) null
                    ,Diagnosis3 varchar(10) null) 


Then the insertion script:

SELECT
 ME.PatientId
,ME.LastName
,ME.FirstName   
,P.ProcedureCodes
,ISNULL(ME.Diagnosis1,'')                AS Diagnosis1
,ISNULL(ME.Diagnosis2,'')                AS Diagnosis2
,ISNULL(ME.Diagnosis3,'')               AS Diagnosis3
FROM vwPatientDemographic ME
LEFT OUTER JOIN #Proc_Concat        P
    ON P.LbPatientId = P.ProcedureCodes
Since #Proc_Concat and #Proc50 only have two columns in play here, PatientID and ProcedureCodes, one of them is throwing the error. 

Guessing not ProcedureCodes as it's pipe-delimeted so must be a varchar. 

That leaves PatientId.  Numeric data types can't handle the space character '1111111 11100', so that's the first place I'd look. 

Another possibility is within your subquery, P1.PatientID = P2.LbPatientID, in case one is a varchar and one is an int, and implicit conversion won't allow the varchar value to be converted to an int to test the equality.

Good luck.