Use LIST function if an element is in multiple categories

SELECT Path+Element "Plant", LIST(Category,', ') "Categories"
FROM (
SELECT eh.name "Element", c.Name "Category", Path
FROM [NuGreen].[Asset].[ElementCategory] ec
, [NuGreen].[Asset].[ElementHierarchy] eh
, [NuGreen].[Asset].[Category] c
WHERE ec.ElementID = eh.ElementID
AND ec.CategoryID = c.ID
AND eh.Name LIKE '%'
AND eh.Level = 2
ORDER BY Element, Category
) AllRelations
GROUP BY Path+Element

Elements in hierarchy level 2 are listed by category. An element that is member of multiple categories has the category names listed in the Categories column.