Combine Multiple Row Values by Comma

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.

For example

Name Email Department
John Doe john@email.com Pediatrics, Med-Peds
Jack Straw jack@email.com Radiology, Internal Medicine

Here's the query thus far:

select u.name,
u.email,
d.department,
r.role
from
(select lastname + ', ' + firstname + ' ' + ISNULL(credentials, '') as name, user_id, email from qi_users where active=1) as u
inner join
(select dept_id, user_id from QI_Users_Departments) as ud
on u.user_id=ud.user_id
inner join
(select dept_id, department from QI_Departments) as d
on ud.dept_id=d.dept_id
inner join
(select role_id, user_id from QI_Users_Roles) as ur
on ud.user_id=ur.user_id
inner join
(select role_id, role from QI_Roles) as r
on ur.role_id=r.role_id
order by 1

Thank you for any help!!

Can show your expected result from query below ?

select * from
(
Select 'name1' as name ,'email' as email , 'KH' as Department , 'admin' as role
union
Select 'name1' as name ,'email' as email , 'TT' as Department , 'admin' as role
)k

Like this

name email Department role

name1 email KH,TT admin

or like this?

name email role KH TT

name1 email admin KH TT

I'm looking for results like the first example.

try this

drop table #temp

select *
into #temp from
(
Select 'name1' as name ,'email' as email , 'KH' as Department , 'admin' as role
union
Select 'name1' as name ,'email' as email , 'TT' as Department , 'admin' as role
)k

declare @column as nvarchar(max)
declare @column2 as nvarchar(max)
declare @sql as nvarchar(max)

set @column =
stuff((
select N','+quotename(y) as [text()] from (
select distinct department as y from #temp) k
order by y
for xml path ('')),1,1,'')
--print @column

set @column2 =
stuff((
select N'+'+''','''+'+'+'isnull('+quotename(y)+','''')' as [text()] from (
select distinct department as y from #temp) k
order by y
for xml path ('')),1,5,'')
--print @column2

set @sql =

N'
select
name
,email
,role
, '+@column2+' as Department
from (
select *
from #temp m pivot (max(department)for department in
('+@column+'))k
)m

'

Print @sql
execute (@sql)