Assign Values of a column in SubQuery in SQL

Question

I am trying to do following in SQL Server:

SELECT 
    PRODUCER_NAME, PRODUCER_ID,
    (SELECT @X = @X + PRODUCT_NAME 
     FROM PRODUCT 
     WHERE PRODUCER_ID = PRODUCER.ID) 
FROM 
    PRODUCER

There are two tables. Producer table is list of all producers. Product table stores product produced by producers. @x is varchar variable

Basically I want a list of all products, comma-separated by producer.

For example

Producer     Products
--------     --------------------------
P1           ProductA,ProductB,ProductC
P2           ProductD,ProductE

I don't know if this is possible this way. Do anyone know how to do this without joining tables?

Answer 1

I don't have a way for you to assign multiple output comma-separated lists to a single varchar variable, but maybe you don't actually need that anyway. Try this:

SELECT Producer = PRODUCER.PRODUCER_NAME, 
       Products = STUFF(
       (
         SELECT N',' + PRODUCT.PRODUCT_NAME 
           FROM dbo.PRODUCT
           WHERE PRODUCT.PRODUCER_ID = PRODUCER.ID 
         FOR XML PATH(''), 
         TYPE).value(N'./text()[1]', N'nvarchar(max)'),1,1,N'')
  FROM dbo.PRODUCER;

On a large table, this kind of correlated subquery can be quite expensive. On SQL Server 2017+ we can use STRING_AGG() in a single pass:

SELECT Producer = PRODUCER.PRODUCER_NAME, 
       Products = STRING_AGG(PRODUCT.PRODUCT_NAME, N',')
FROM dbo.PRODUCT
INNER JOIN dbo.PRODUCER
  ON PRODUCT.PRODUCER_ID = PRODUCER.ID
GROUP BY PRODUCER.PRODUCER_NAME;

• Example db<>fiddle