First time posting and not a SQL expert by any means!
Aim: I want to combine multiple row values (b.[Canvasser Name]) into one combined cell (f.canvassers) and check f.canvasser doesn't contain the same value as aa.[Primary Canvasser]. All of these fields are text.
Problem: I'm encountering a problem where by f.canvassers automatically puts , at the end of the value (how can I correct this?) and also my NOT IN/LIKE statement doesn't pick up on the , either so it's returning values that have the same primary canvasser but also correctly returning values where the primary and proposal canvasser do not match.
SELECT aa.[Cons ID], aa.name, aa.[Primary Canvasser], f.[Proposal Import ID], f.Canvassers
FROM [Data_Team].[dbo].[vt_lu_ConstituentBio] as AA
LEFT JOIN
(SELECT DISTINCT a.[Proposal Import ID], a.[Constituent Cons ID],
( SELECT b.[Canvasser Name] + ', ' AS [text()]
FROM [Data_Team].[dbo].[vt_lu_Proposals] B
WHERE a.[Proposal Import ID]=b.[Proposal Import ID]
ORDER BY a.[Proposal Import ID]
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') [Canvassers]
FROM [Data_Team].[dbo].[vt_lu_Proposals] A
where a.[Proposal Active?] = 'active')
as F on f.[Constituent Cons ID]= aa.[Cons ID]
WHERE aa.[Primary Canvasser] NOT IN ('%f.Canvassers%')
ORDER BY aa.[Cons ID]