how to use COALESCE in an inner join statement in sql

Question

im planning to use COALESCE in my problem. All i want is to have values separated by commas in a single column. here is my statement:

select p.[name], cd.CustomerName, cd.CustomerEmailID
   ,cd.CustomerPhoneNo,cd.CustomerAddress
   ,cd.TotalPrice,cd.OrderDateTime, cd.PaymentMethod 
FROM CustomerDetails cd 
Inner Join CustomerProducts cp ON cp.CustomerID = cd.Id 
Inner Join Products p ON cp.ProductID = p.ProductID

and it will give me this

now i want the product column to have a result of

carbon dioxied,industrial oxygen

since they are in the same ID.

please me help me out. thank you so much

UPDATE: fiddle

UPDATE: current statement but still gives me the same result.

use ShoppingCartDB
select 
      STUFF((SELECT ',' + p.[name]
             FROM  Products p
             WHERE cp.ProductID = p.ProductID
             FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,1,'') AS Name
     , cd.CustomerName, cd.CustomerEmailID
     ,cd.CustomerPhoneNo,cd.CustomerAddress
     ,cd.TotalPrice,cd.OrderDateTime, cd.PaymentMethod 
FROM CustomerDetails cd 
Inner Join CustomerProducts cp ON cp.CustomerID = cd.Id 

Answer 1

SELECT DISTINCT
      STUFF((SELECT ',' + p.[name]
             FROM  Products p
             WHERE cp.ProductID = p.ProductID
             FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,1,'') AS Name
     , cd.CustomerName, cd.CustomerEmailID
     ,cd.CustomerPhoneNo,cd.CustomerAddress
     ,cd.TotalPrice,cd.OrderDateTime, cd.PaymentMethod 
FROM CustomerDetails cd 
Inner Join CustomerProducts cp ON cp.CustomerID = cd.Id