SQL Server - Concatenate - Stuff doesn't work

Question

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?

`STUFF` doesn't concatenate *anything* here. It's just removing 1 characters from the position `COUNT(ID)`. It's `FOR XML PATH` that is concatenating. — Thom A, Jan 25 '22 at 14:48
Does this answer your question? [String_agg for SQL Server before 2017](https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-before-2017) — Thom A, Jan 25 '22 at 14:49

M.Ali · Accepted Answer · 2022-01-25T14:59:50.353

3

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;

edited Jan 25 '22 at 14:59

answered Jan 25 '22 at 14:52

M.Ali

67,945
13
101
127

Amazing, the first query works fine, but in the concatenated column includes an space at beginning. How can I do a Trim for this? – Ferran Muñoz Jan 25 '22 at 14:58
I have removed the space now, I have changed `.value('.','NVARCHAR(MAX)'),1,2,' ') ` to `.value('.','NVARCHAR(MAX)'),1,2,'') ` – M.Ali Jan 25 '22 at 15:00
Oh yeah, it was the space near the 2 at the end. I change it for '' and now works! – Ferran Muñoz Jan 25 '22 at 15:01

SQL Server - Concatenate - Stuff doesn't work

1 Answers1