SQL to get multiple rows value as comma separated

Question

I have three tables of data like below. I want to join these three tables on which one column value should be comma separated as shown in the expected output. How to achieve this.

Tbl_user_vs_roles

Staff_id    Role_id
111         10
111         20
111         30
222         20
333         30

Tbl_roles

Role_id     Role_name
10          Operator
20          Manager
30          Head

Tbl_user_details

Staff_id   Staff_name    Phone_no
 111       Niya          12345678
 222       Ram           12345677
 333       Varun         12345688

Output should show the details role_id<30

Staff_id         Staff_name     Roles                   Phone_no
   111            Niya         Operator,Manager         12345678
   222            Ram          Manger                   12345677

I have tried like below. But with along the expected result it also returns the rows >=30 with NULL as Roles.

  SELECT Tbl_user_details.Staff_id, Tbl_user_details.Staff_name,
  Tbl_user_details.Phone_no,STUFF((SELECT ',' + RTRIM(j.Role_name) FROM Tbl_roles j  JOIN Tbl_user_vs_roles
k ON j.Role_id = k.Role_id WHERE Tbl_user_details.Staff_id, = k.Staff_id AND k.Role_id <30 ORDER BY j.Role_name FOR XML PATH('')),1,1,'') AS 'Roles'  FROM Tbl_user_details
group by Tbl_user_details.Staff_id,Tbl_user_details.Staff_name, 
Tbl_user_details.Phone_no

I got the output as:

 Staff_id        Staff_name     Roles                   Phone_no
   111            Niya         Operator,Manager         12345678
   222            Ram          Manger                   12345677
   333            Varun        NULL                     12345688

score 0 · Accepted Answer · answered Mar 10 '23 at 21:14

You are only applying the conditions to the select statement within the STUFF. If you would like this to be taken into account external to the STUFF then I would add the correct conditions to the overall select statement, or contain the the overall statement within a derived table and condition with
WHERE [Roles] IS NOT NULL

Conditions to SELECT statement:

SELECT  Tbl_user_details.Staff_id
        ,Tbl_user_details.Staff_name
        ,Tbl_user_details.Phone_no,
        STUFF(
            (   SELECT ',' + RTRIM(j.Role_name)
                FROM    Tbl_roles j  JOIN 
                        Tbl_user_vs_roles k ON 
                            j.Role_id = k.Role_id 
                WHERE   Tbl_user_details.Staff_id, = k.Staff_id AND 
                        k.Role_id <30 
                ORDER BY j.Role_name FOR XML PATH('')),1,1,'') AS 'Roles'  
FROM    Tbl_user_details
WHERE <Add conditions here>
group by    
        Tbl_user_details.Staff_id
        ,Tbl_user_details.Staff_name
        ,Tbl_user_details.Phone_no

OR

Using derived table:

SELECT  *
FROM    (
        SELECT  Tbl_user_details.Staff_id
                ,Tbl_user_details.Staff_name
                ,Tbl_user_details.Phone_no,
                STUFF(
                    (   SELECT ',' + RTRIM(j.Role_name)
                        FROM    Tbl_roles j  JOIN 
                                Tbl_user_vs_roles k ON 
                                    j.Role_id = k.Role_id 
                        WHERE   Tbl_user_details.Staff_id, = k.Staff_id AND 
                                k.Role_id <30 
                        ORDER BY j.Role_name FOR XML PATH('')),1,1,'') AS 'Roles'  
        FROM    Tbl_user_details
        group by    
                Tbl_user_details.Staff_id
                ,Tbl_user_details.Staff_name
                ,Tbl_user_details.Phone_no) v

WHERE [Roles] IS NOT NULL

SQL to get multiple rows value as comma separated

1 Answers1