I have a query that works:

DECLARE @ProductID int
SET @ProductID = '1234'

SELECT DISTINCT TOP 12 a.ProductID
FROM A a
WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE b.ProductID = @ProductID)
AND a.ProductID != @ProductID

It returns a list of 12 product numbers, all unique.

I need to store these results in a variable, comma separated, because that's what 3rd party stored procedure needs. So I have this:

 DECLARE @ProductID int
 DECLARE @relatedprods varchar(8000)
 SET @ProductID = '1234'
 SET @relatedprods = ''

 SELECT TOP 12 @relatedprods = @relatedprods + CONVERT(VARCHAR(20), a.ProductID) + ', '
   FROM A a
   WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE B.ProductID = @ProductID)
   AND a.ProductID != @ProductID

SELECT @relatedprods

Now, none of these are distinct, but it is returning 12 rows.

Now I add the 'distinct' back in, like in the first query:

 DECLARE @ProductID int
 DECLARE @relatedprods varchar(8000)
 SET @ProductID = '1234'
 SET @relatedprods = ''

 SELECT DISTINCT TOP 12 @relatedprods = @relatedprods + CONVERT(VARCHAR(20), a.ProductID) + ', '
   FROM A a
   WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE B.ProductID = @ProductID)
   AND a.ProductID != @ProductID

 SELECT @relatedprods

Only one product is returned in the comma separated list! Does 'distinct' not work in assignment statements? What did I do wrong? Or is there a way to get around this?

Thanks in advance!