concatnate values for different rows

Hi ,

I have a situation where i need to concatnate values from different rows and store it a one string.

sample

dealid date

1 1/5/2007

1 2/4/2009

2 5/5/2004

2 8/5/2006

2 4/8/2006

so for one particular deal how many ever dates there are , i need to concatnate them all separated by a comma(,) and return and one string.

Is ther any way i could do it, Any suggestions appreaciated

Thanks

Ashsih

Maybe something like:

select distinct dealId,
reverse(substring(reverse(
( select convert(varchar(10), date, 101) + ', ' as [text()]
from theTable b
where a.dealId = b.dealId

order by date desc
for xml path('')
)), 3, 300))
from theTable a

|||

For a particular DealD:

declare @.targetDeal int set @.targetDeal = 1

select reverse(substring(reverse

( select convert(varchar(10), date, 101) + ', ' as [text()]
from theTable

where dealId = @.targetDeal

order by date desc
for xml path('')

), 3, 300)) as DealDates