Combine multiple rows into one column

[bmacbmac]

Hello..I have existing data in a #temp table

create table #temp (recordid int, name varchar(200))
insert into #temp values (1, 'NAME1')
insert into #temp values (1, 'NAME2')
insert into #temp values (1, 'NAME3')
insert into #temp values (2, 'NAME1')
insert into #temp values (2, 'NAME2')
insert into #temp values (3, 'NAME1')
insert into #temp values (4, 'NAME1')
insert into #temp values (4, 'NAME2')
insert into #temp values (4, 'NAME3')

I would like to capture the recordid and all of the associated names (separated by a semicolon) into one column. Something like this in a #temp2 table or something

RECORDID, NAMES
1, NAME1;NAME2;NAME3
2, NAME1;NAME2
3, NAME1
4, NAME1;NAME2;NAME3

Is it possible to do this in SQL 2000? I kind of have a dim-witted way to do it with a ton of joins and temp tables but never can adjust correctly for the different numbers of names with each recordid.

Thanks!

 

[bborissov]

You better make a function.

CREATE FUNCTION dbo.JoinNames
(@Id int)
RETURNS varchar(8000)
AS
BEGIN
   DECLARE @Names varchar(8000)
   SET @Names = ''
   SELECT @Names = @Names + Name + ','
          FROM #Temp
   WHERE recordid = @Id
   RETURN LEFT(@Names, LEN(@Names)-1)
END

*** Then
SELECT RecordId, dbo.JoinNames(RecordId) AS Names
FROM #Temp
GROUP BY RecordId

Not tested!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[bmacbmac]

That's perfect! I have never used a Function before. Thanks for the heads up.