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
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
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