Assign Values of a column in SubQuery in SQL

sql, sql-server, subquery

Solution

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

Problem

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?

Original source