I need to combine multiple rows into one row with one column containing multiple values separated by a comma. In this case, users can be assigned to multiple departments, so this query will return separate rows for each assignment. I'd like to combine those in to a single row.
Here's the query thus far:
(select lastname + ', ' + firstname + ' ' + ISNULL(credentials, '') as name, user_id, email from qi_users where active=1) as u
(select dept_id, user_id from QI_Users_Departments) as ud
(select dept_id, department from QI_Departments) as d
(select role_id, user_id from QI_Users_Roles) as ur
(select role_id, role from QI_Roles) as r
order by 1
Thank you for any help!!