Turning multiple rows into concatenated string in one column

[thementornetwork]

Let's say I have a table that looks like this:

ID Year
1 2007
1 2008
1 2009
2 2001
2 2002
2 2009


Now I need to run a query and return the data to like this:

ID Years
1 2007, 2008, 2009
2 2001, 2002, 2009

Any easy way of writing a query to return this output? Thanks.

 

[gmmastros]

Read this:

thread183-1159740


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

See also
thread183-1556857

 

[RyanEK]

If you're using SQL2005+ you can do it in one query...

declare @temp table (id int, yr int)
insert into @temp values (1,2007)
insert into @temp values (1,2008)
insert into @temp values (1,2009)
insert into @temp values (2,2001)
insert into @temp values (2,2002)
insert into @temp values (2,2009)

select t1.id, t2.years
from @temp t1
join ( 
  select id, 
  replace((     
    select yr as 'data()'     
	from @temp t2     
	where t1.id = t2.id     
	for xml path('')),' ',',') as years 
	from @temp t1 
	group by id
) as t2 on t1.id = t2.id
group by t1.id, t2.years

Ryan