Posted by Divya, Feb. 20, 2023, 2:35 a.m.

SQL to get multiple rows value as comma separated

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

Answer

Answers

Comment

Which database is this? For Postgres, use string_agg() to merge multiple rows into the same row.

Step 1: do an inner join of all 3 tables to get staff_id, staff_name and roles;
Step 2: collapse the roles column with string_agg or something similar and group by the first 2 columns.

Kerry (53)

Feb. 20, 2023, 11:23 a.m.

Comment

Depending on which SQL flavor you're using, STRING_AGG or GROUP_CONCAT is definitely the way to go for this. I usually run a quick check on the output to make sure the delimiters didn't get messed up by null values. I’ve been using this https://www.thetoolapp.com/comma-seperator-online/ to double-check my lists or reformat them quickly after exporting the query results. It saves a lot of manual cleanup!

Pitter (2)

April 23, 2026, 3:43 a.m.