SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
/* There are two tables. 
   Producer table is list of all producers. */
CREATE TABLE dbo.PRODUCER
(
  ID            int, 
  PRODUCER_NAME nvarchar(32)
);

/*  Product table stores product produced by producers. */
CREATE TABLE dbo.PRODUCT
(
  ID           int, 
  PRODUCER_ID  int,
  PRODUCT_NAME nvarchar(32)
);

/*  Desired results:
    
    Producer     Products
    --------     --------------------------
    P1           ProductA,ProductB,ProductC
    P2           ProductD,ProductE            */
    
INSERT dbo.PRODUCER(ID, PRODUCER_NAME) VALUES (1, N'P1'), (2, N'P2');

INSERT dbo.PRODUCT(ID, PRODUCER_ID, PRODUCT_NAME)
  VALUES (1, 1, N'ProductA'),(2, 1, N'ProductB'),
         (3, 2, N'ProductC'),(4, 2, N'ProductD'),
         (5, 2, N'ProductE');
7 rows affected

/* on vintage versions of SQL Server */

    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;


Producer
Products


P1
ProductA,ProductB

P2
ProductC,ProductD,ProductE


/* on SQL Server 2017+ */

    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;


Producer
Products


P1
ProductA,ProductB

P2
ProductC,ProductD,ProductE