Alternate approach to STRING_AGG with multiple table JOINS (SQL Server 2016)

Question

CONTEXT

I am working with SQL Server 2016. I am trying to group by User table's id and username and get the roleDesc from Role table, as comma separated values.

• The #userTable and #roleTable tables are mapped by #mapTable.
• One user can have multiple roles

Required result:

id	username	roleDesc
1	user1	role1,role2
2	user1	role2

Issue

As I am using SQL Server 2016, I am unable to use STRING_AGG to group the roleDesc into comma separated values.

My attempt

I tried using STUFF, but get an error.

IF(OBJECT_ID('Tempdb..#userTable') IS NOT NULL) 
    DROP TABLE #userTable

CREATE TABLE #userTable
(
    id int, 
    username nvarchar(200)
) 

INSERT INTO #userTable 
VALUES (1, 'user1'), 
       (2, 'user2') 
   
IF (OBJECT_ID('Tempdb..#roleTable') IS NOT NULL) 
    DROP TABLE #roleTable

CREATE TABLE #roleTable
(
    id int, 
    roleDesc nvarchar(200)
) 

INSERT INTO #roleTable 
VALUES (1, 'role1'), 
       (2, 'role2'), 
       (3, 'role3') 
  
IF(OBJECT_ID('Tempdb..#mapTable') IS NOT NULL) 
    DROP TABLE #mapTable

CREATE TABLE #mapTable
(
    id int, 
    map_userId int, 
    map_roleId int
) 

INSERT INTO #mapTable 
VALUES (1, 1, 1), 
       (2, 1, 2), 
       (3, 2, 2)
  
SELECT 
    U.id, 
    U.username,
    -- STRING_AGG(R.roleDesc,',')  roleDesc --> this works in SQL Server 2017
    STUFF ((SELECT 
                ',' + R.roleDesc 
            FROM 
                #roleTable T
            WHERE 
                T.id = R.id 
            FOR XML PATH('')), 1, 1, '') AS roleDesc 
FROM 
    #userTable U
JOIN 
    #mapTable MAP ON MAP.map_userId = U.id
JOIN 
    #roleTable R ON R.id = MAP.map_roleId
GROUP BY 
    U.id,  U.username

Links I referred to:

1. https://stackoverflow.com/a/19348687/7067740
   • The where clause Id inside the STUFF is ultimately used in the outer query GROUP BY. This is not true in my case.
2. https://stackoverflow.com/a/56964302/7067740
   • This is closer to my required solution, but the the STUFF query has a simpler WHERE clause. I am not able to translate it to my case.

I get this error:

Column '#roleTable.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Question

What could be the alternate approach?

Answer 1

You don't need #roleTable and #mapTable on the outer query. Move it to the sub-query

SELECT 
    U.id, 
    U.username,
    STUFF ((SELECT 
                ',' + R.roleDesc 
            FROM 
                #roleTable r
            JOIN
                #mapTable m 
            ON
                r.id = m.map_roleId
            WHERE 
                m.map_userId = U.id
            FOR XML PATH('')), 1, 1, '') AS roleDesc 
FROM 
    #userTable U
GROUP BY 
    U.id,  U.username