Creating comma-delimited strings with variable amounts of data

[Azathoth]

I have a stored procedure that has to return values for particular product_ids. The problem here is that one result must be returned for each product_id, and there's no way of telling how many results are going to be returned. I'm trying to come up with a way to dynamically concatenate the results, if more than 1 is returned. Here is the code that leads up to the dilemma:

	declare @query varchar(1000)
	declare @id_count int

	select
		@id_count = count(distinct product_id)
	from
		#result

	select @query = '
	insert into #final (
		product_id,
		product_code,
		tpc_description,
		tpsc_description,
		status_code,
		location_code,
		status_datetime,
		lab_tech_1 )
	select
		r.product_id,
		r.product_code,
		r.tpc_description,
		r.tpsc_description,
		r.status_code,
		r.location_code,
		r.status_datetime,
		r.lab_tech_1
	from 
		#result r
			inner join (
				select top ' + convert(varchar, @id_count)  + '
					product_id,
					dt = max(status_datetime)
				from
					#result
				group by
					product_id ) rr
				on
					r.product_id = rr.product_id
					and r.status_datetime = rr.dt'

	exec (@query)

The number of results that need to be returned is contained in the variable @id_count, but I'm still not sure how to write a statement that will be able to concatenate different amounts of results. Table #final contains the the rows of values that need to be returned, and I'd like to put the results into variables in the form of comma-delimited strings so they can be OUTPUT. Any ideas?

 

[ESquared]

SET @ConcatVar = ''
SET @ConcatVar = @ConcatVar + ',' + ConcatField
   FROM TableWithFields
SET @ConcatVar = SubString(@ConcatVar,2,8000)

-------------------------------------
A sacrifice is harder when no one knows you've made it.