Displays a value that has a column with multiple values

Question

I have recipes that have many products - many-to-many relationships, and in the view table I want to present recipe and products in one line like that:

create view AllRecipe as
select Recipe.RecipeName,Recipe.Picture,Recipe.Scoring,Recipe.Preparation,Product.ProductName
from Recipe join ProductsRecipe on Recipe.Id=ProductsRecipe.RecipeId
join Product on ProductsRecipe.Product=Product.Id

and the result:

___ RecipeName   Picture   Scoring   Preparation   ProductName
1   salad        null      7         Cut and mix   cucumber
2   salad        null      7         cut and mix   tomato

but I want:

___ RecipeName   Picture   Scoring   Preparation   ProductName
1   salad        null      7         Cut and mix   cucumber tomato

how can I do that?

@daniherrera sqls2017 has string_agg so it's no need to simulate now.. hence the version query — Caius Jard, Nov 26 '18 at 17:17

Ela · Accepted Answer · 2018-11-26T18:16:40.193

this is the answer!! thank you, guys!!

the ans is from here: simulating-group-concat-mysql-function-in-microsoft-sql-server-2005

very helpful!

SELECT Recipe.RecipeName,Recipe.Picture,Recipe.Scoring,Recipe.Preparation, STUFF(
             (SELECT ',' + ProductName
              from Recipe
    join ProductsRecipe on Recipe.Id=ProductsRecipe.RecipeId
    join Product on ProductsRecipe.Product=Product.Id
              FOR XML PATH (''))
             , 1, 1, '')
    from Recipe
    join ProductsRecipe on Recipe.Id=ProductsRecipe.RecipeId
    join Product on ProductsRecipe.Product=Product.Id
group by Recipe.RecipeName,Recipe.Picture,Recipe.Scoring,Recipe.Preparation

Displays a value that has a column with multiple values

1 Answers1