Use Stuff With Inner Join Query

I have three tables in my database. A table for Product, a table for Types and a mapping table named Prod_Type. My database is sql server that's why I cant use the group_concat fun

Solution 1:

Group concatenation queries can be difficult to phrase in SQL Server, at least for earlier versions which do not have a STRING_AGG function. The trick is that the outer query should act on the table whose keys have values you want to aggregate from joining to one or more other tables. In this case, we put ProductTable on the outside, and then aggregate over everything else, to generate a CSV list of types for each product.

SELECT
    p.Prod_ID,
    p.Name,
    TypeName = STUFF((
        SELECT','+ t.TypeName
        FROM Prod_TypeTable pt
        INNERJOIN TypeTable t
            ON pt.Type_ID = t.Type_ID
        WHERE pt.Prod_IDM = p.Prod_ID
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM ProductTable p
ORDERBY p.Prod_ID;

Demo

Solution 2:

If you are using SQL Server 2017+, I recommend using STRING_AGG as mentioned by @tim-biegeleisen. STRING_AGG concatenates the values of string expressions and places separator values between them (not added at the end of the string).

SELECT
      p.Prod_ID,
      p.Name,
      TypeName = (
      SELECT STRING_AGG ( t.TypeName, ',')FROM Prod_TypeTable pt
          INNER JOIN TypeTable t
              ON pt.Type_ID = t.Type_ID
          WHERE pt.Prod_IDM = p.Prod_ID
    )
FROM ProductTable p
ORDERBY p.Prod_ID;

To know more about concatenation of queries in SQL Server, I have written a blog at the link below. https://blog.vcillusion.co.in/understanding-the-grouped-concatenation-sql-server/