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