I have an existing query that pulls recipe information using multiple joins, but need to add allergen information. I have a query that pulls the data I need, but it returns multiple rows, one row for each allergen. I need a single row for each recipe that lists the allergens as a single string separated by commas.
This is what I have so far:
SELECT MenRecipeHeader.RecipeNumber, MenRecipeHeader.RecipeName, InvAllergies.Allergy FROM InvItemsAllergens INNER JOIN InvAllergies ON InvItemsAllergens.InvAllergyID = InvAllergies.InvAllergyID INNER JOIN InvItems ON InvItemsAllergens.InvItemsID = InvItems.InvItemID INNER JOIN MenFoodItems INNER JOIN MenRecipeHeader ON MenFoodItems.MenFoodItemID = MenRecipeHeader.MenFoodItemID ON InvItems.MenFoodItemID = MenFoodItems.MenFoodItemID WHERE (MenRecipeHeader.RecipeNumber = N'60218')
But, it returns one row per allergen....
RecipeNumber RecipeName Allergy 60218 YOGURT PARFAIT BERRY ALC Dairy 60218 YOGURT PARFAIT BERRY ALC Egg 60218 YOGURT PARFAIT BERRY ALC Treenuts 60218 YOGURT PARFAIT BERRY ALC Wheat 60218 YOGURT PARFAIT BERRY ALC Soy
I need a single row for each recipe where the allergens are all in a single string separated by commas. Like this: Dairy, Egg, Treenuts, Wheat, Soy
How can I revise the existing query to return the allergens so each recipe has only one row?