If you can use the SQLCLR here is an option that is a bit more expressive in terms of the syntax:

SELECT p.Prod_Name,

dbo.GROUP_CONCAT(c.Cat_Name)

FROM #Products p

JOIN #Product_Categories pc ON p.Prod_ID = pc.Prod_ID

JOIN #Categories c ON pc.Cat_ID = c.Cat_ID

GROUP BY Prod_Name

You can download the dbo.GROUP_CONCAT function here: http://groupconcat.codeplex.com