SQL Server – Concatenate – Stuff doesn’t work

I have this query in SQL Server and I want to concatenate using stuff the ‘Products’ column, but I don’t know why this doesn’t work…

SELECT BrandsProvider.id, BrandsProvider.Name, 
    (SELECT (STUFF((
        SELECT ','+ CONVERT(NVARCHAR(MAX), sku) 
            FROM items it2
        WHERE it2.sku = it.sku
        FOR XML PATH('')),
        COUNT('ID'), 1, ''))) AS 'Products'
FROM items it
    INNER JOIN BrandsProvider ON it.IdBrandProduct = BrandsProvider.id

And the result is:

Id     Name             Products
--------------------------------
1      BRAND EXAMPLE     PR344
1      BRAND EXAMPLE     PR345

And I want this:

Id     Name             Products
--------------------------------
1      BRAND EXAMPLE     PR344, PR345

Also I used SELECT DISTINCT in the query but the result is the same…

So, where can be the mistake?

Answer

SELECT  b.id
    ,   b.Name
    ,   STUFF((SELECT ', ' + CAST(i.sku AS VARCHAR(10)) [text()]
         FROM items i 
         WHERE i.IdBrandProduct = b.id
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,'') Products
FROM BrandsProvider b
GROUP BY b.id
    ,   b.Name

SQL Server 2017 and Later Versions

If you are working on SQL Server 2017 or later versions, you can use built-in SQL Server Function STRING_AGG to create the comma delimited list:

SELECT b.id
    ,   b.Name
    ,  STRING_AGG(i.sku, ', ') AS Products
FROM BrandsProvider b
INNER JOIN items i  ON i.IdBrandProduct = b.id
GROUP BY b.id
    ,   b.Name;