set nocount on

create table #temp

(product_id int not null,

prod_desc char(1) null)

insert into #temp

select 1, 'A'

insert into #temp

select 1, 'B'

insert into #temp

select 1, 'C'

insert into #temp

select 2, 'D'

insert into #temp

select 2, 'E'

insert into #temp

select 2, 'F'

DECLARE @Output VARCHAR(8000)

SET @Output = ''

SELECT @Output = @Output + prod_desc + ', '

FROM #temp

WHERE Product_id = 1

ORDER BY product_id

Select left(@Output, len(@Output) -1) as output

drop table #temp