Use STUFF with INNER JOIN Query

Question

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 function and I am using the Stuff function.My table structures were as follows

ProductTable

Prod_ID    |   Name    |   Brand
-------        ----        -----
   1       |   Name1   |   Brand1
   2       |   Name2   |   Brand2
   3       |   Name3   |   Brand3
   4       |   Name4   |   Brand4
   5       |   Name5   |   Brand5
   6       |   Name6   |   Brand6
   7       |   Name7   |   Brand7

TypeTable

 Type_ID   |   TypeName
 -------       --------
    1      |   TypeName1
    2      |   TypeName2
    3      |   TypeName3
    4      |   TypeName4
    5      |   TypeName5

Prod_TypeTable

  Prod_IDM   |  Type_ID
  --------      -------
     1      |     1
     1      |     3
     1      |     4
     1      |     5
     2      |     2
     2      |     3
     3      |     4
     4      |     5
     4      |     1
     5      |     4
     5      |     3
     5      |     2
     6      |     2
     6      |     3
     7      |     5

I was able to join the table of product to the mapping table of Prod_type. I used stuff query to avoid multiple results. My query was something like this:

Select 
  top 5 * 
from  ProductTable 
   inner Join (SELECT  
                 Prod_IDM, 
                 STUFF((SELECT ', ' + CAST(Type_ID AS VARCHAR(10)) [text()]
                         FROM Prod_TypeTable 
                         WHERE Prod_IDM = t.Prod_IDM FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') TypeID
               FROM Prod_TypeTable t
               GROUP BY Prod_IDM   )  As TypeList on TypeList.Prod_IDM = ProductTable.Prod_ID

What I need to do now is to join my previous query result to type table in able to get the names of the types respectively. How can I possibly do that?My expected output would be like this

Prod_ID    |   Name    |  TypeName
-------        ----       ---------
   1       |   Name1   |  TypeName1,  TypeName3,  TypeName4,  TypeName5
   2       |   Name2   |  TypeName2,  TypeName3
   3       |   Name3   |  TypeName4
   4       |   Name4   |  TypeName5,  TypeName1
   5       |   Name5   |  TypeName4,  TypeName3,  TypeName2
   6       |   Name6   |  TypeName2,  TypeName3
   7       |   Name7   |  TypeName5 

Answer 1

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
ORDER BY 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/

Answer 2

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
        INNER JOIN 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
ORDER BY p.Prod_ID;

Demo