How to get distinct columns using COALESCE

Hi guys, can you please help me to solve this problem. I have to get distinct row from offering column of xyz table.

I have to get offering1, offering2 from xyz table. But I am getting only offering1. I should not get duplicate rows from XYZ table.

SELECTDISTINCT @.Staging_Off=COALESCE(@.Staging_Off+',','')+ Offering

FROM xyz

WHERE xyz.OfferingNOTIN

(SELECTDISTINCT Offering.Offering

FROM OfferingJoin xyz

ON Offering.Offering= xyz.Offering

AND Offering.SourceSystem= @.SourceSystem

)

That's probably because your nested query (SELECT DISTINCT) is not working on the same row as your outer query. You must make sure your inner query join with a value of the outer query, otherwise they won't be related. Something like this:

SELECT DISTINCT @.Staging_Off=COALESCE(@.Staging_Off +',','')+ OfferingFROM xyzAS X1WHERE X1.OfferingNOT IN(SELECT DISTINCT O.OfferingFROM OfferingAS O, xyzAS X2WHERE O.Offering = X2.OfferingAND X2.SomeId = X1.SomeIdAND O.SourceSystem= @.SourceSystem)

Here I assume that there are some kind of unique id in xyz table that can be used

I think this query could be rewritten in a more clean manner, but I can't do it from the tip of my head. Would need source data and do some trial and erroring ;-) Good luck!