Combined data from several rows into one row

[bmacbmac]

Hi,

I have the following table:

create table #temp (irecordid int, names varchar(50))
go
insert into #temp values (1, 'SMITH')
insert into #temp values (1, 'JOHNSON')
insert into #temp values (1, 'ANDERSON')
insert into #temp values (1, 'JENKINS')
insert into #temp values (2, 'MATTHEWS')
insert into #temp values (2, 'MORRIS')
insert into #temp values (2, 'RICHFIELD')
insert into #temp values (2, 'CHATFIELD')
insert into #temp values (2, 'HENRY')

I need to combine all of the 'names' information into one single row grouped by the irecordid. I need to end up with:

Irecordid Names
1 SMITH JOHNSON ANDERSON JENKINS
2 MATTHEWS MORRIS RICHFIELD CHATFIELD HENRY

I have played around with several comborsome methods and thought I would see what ideas people had to do this?

Thanks!

Brian

 

[TamarGranor]

Try this:

SELECT iRecordID, 
       STUFF((  SELECT ' ' + Names
          FROM #temp T2
          WHERE T1.iRecordID = T2.irecordid
          FOR XML PATH('')), 1, 1, '') AllNames
FROM #temp T1
GROUP BY irecordID
ORDER BY 1

Tamar

 

[bmacbmac]

Brilliant! Worked like a charm!