SQLTeam.com | Weblogs | Forums

Combine Multiple Row Values by Comma


#1

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


#2

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


#3

Like this

name email Department role

name1 email KH,TT admin

or like this?

name email role KH TT

name1 email admin KH TT


#4

I'm looking for results like the first example.


#5

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)