Using STUFF in order to get multiple elements into one block in SQL

Question

I have three tables.

1st Table is called Customers with this dummy info in it:

2nd table is called Customer_Product with this dummy info in it:

3rd table is called Products with this dummy info in it:

And the diagram looks like this:

I currently have this code that I wrote:

SELECT 
    A.Customer_Name [Customer Name], 
    (SELECT CASE
        WHEN A.Customer_ID = A.Customer_Parent_ID
            THEN 'Is the Parent'
        ELSE(
        SELECT F.Customer_Name 
        FROM dbo.Customers F 
        Where F.Customer_ID = A.Customer_Parent_ID)
     END)  [Customer Parent's Name],
    C.Product_Name [Product They Own]
FROM dbo.Customers A 
JOIN Customer_Product B ON A.Customer_ID = B.Customer_ID
JOIN Products C ON C.Product_ID = B.Product_ID

That gives this output:

My question is how can I write a query that instead of what its currently giving me, it would give me an output that looks like this:

Thank you for any and all help! I'm new to SQL so an answer here doesn't really stand out to me, that current code is the closest I can seem to get to what I want. I realize I'll need to use STUFF, but I don't understand how. I've been taking a look at this post (how to get name from another table with matching id in another table?) and trying to use it to work for me, but with little luck.

Answer 1

Make the script you have a CTE. Then, once you define it as a CTE, you can run this.

SELECT Customer_name,
      STUFF((
        SELECT ', ' + [Product They Own] 
        FROM CTE
        WHERE Customer_name= final.Customer_name
FOR XML PATH('')),1,1,'') AS [Products They Own] 
FROM CTE final
GROUP BY Customer_name