String Combination

[sjeya]

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'

select *
from #temp
product_id prod_desc
----------- ---------
1 A
1 B
1 C
2 D
2 E
2 F

Instead of dispaying as represented above, I need to display this as

product_id prod_desc
----------- ---------
1 A, B, C
2 D, E, F

How can I achieve this?

Thank you,
Sheba

[MAK]

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'


select product_id,
MAX(CASE LineNO1 WHEN 1 THEN Prod_Desc ELSE NULL END) AS Product1,
MAX(CASE LineNO1 WHEN 2 THEN Prod_Desc ELSE NULL END) AS Product2,
MAX(CASE LineNO1 WHEN 3 THEN Prod_Desc ELSE NULL END) AS Product3
FROM (select product_id,Prod_Desc, LineNo1=
Case when product_id = product_id then
(select count(*) from #temp a where
a.product_id= b.product_id and
convert(varchar(10),a.product_id)+a.prod_desc < convert(varchar(10),a.product_id)+b.prod_desc)+1 end
from #temp b) as Mytable
GROUP BY product_id
GO

--results
product_id,Product1,Product2,Product3
1,A,B,C
2,D,E,F

or

select product_id,product_description=Product1+','+Produc t2+','+Product3
from(
select product_id,
MAX(CASE LineNO1 WHEN 1 THEN Prod_Desc ELSE NULL END) AS Product1,
MAX(CASE LineNO1 WHEN 2 THEN Prod_Desc ELSE NULL END) AS Product2,
MAX(CASE LineNO1 WHEN 3 THEN Prod_Desc ELSE NULL END) AS Product3
FROM (select product_id,Prod_Desc, LineNo1=
Case when product_id = product_id then
(select count(*) from #temp a where
a.product_id= b.product_id and
convert(varchar(10),a.product_id)+a.prod_desc < convert(varchar(10),a.product_id)+b.prod_desc)+1 end
from #temp b) as Mytable
GROUP BY product_id) as x
GO

--results
product_id,product_description
1,A,B,C
2,D,E,F