Monday, November 22, 2010

Multiple Rows to column.

create table #Test (
ProdID varchar(5),
ProdDesc varchar(256)
)

insert into #Test (
ProdID,
ProdDesc
)
select 'Prod1', 'Description 1' union all
select 'Prod1', 'Description 2' union all
select 'Prod2', 'Description 1' union all
select 'Prod3', 'Description 1' union all
select 'Prod3', 'Description 2' union all
select 'Prod3', 'Description 3' union all
select 'Prod3', 'Description 4'


select distinct
a.ProdID,
stuff((select
',' + b.ProdDesc
from
#Test b
where
b.ProdID = a.ProdID
for xml path('')),1,1,'') as ProdDesc
from
#Test a

No comments: