面试中遇到的一个sql查询题，请大神指教

昨天去面试，遇到一道SQL题，不会解，先贴上题目：

year	dept
2008	dev
2008	sale
2009	dev
2010	hr
2010	man
2010	market
2011	service

需求：写一个sql实现以下查询结果：

year	dept
2008	dev,sale
2009	dev
2010	hr,man,market
2011	service

请问如何写这个sql？？

---

create table #testTable(year varchar(4),dept varchar(16))

insert into #testTable values('2008','dev')
insert into #testTable values('2008','sale')
insert into #testTable values('2009','dev')
insert into #testTable values('2010','hr')
insert into #testTable values('2010','man')
insert into #testTable values('2010','market')
insert into #testTable values('2011','service')

select year,dept=STUFF((select ','+ltrim(dept) from #testTable where year=t.year for XML path('')),1,1,'')
from #testTable t
group by year

-- drop table #testTable

---

select year,GROUP_CONCAT(dept) from tablex group by year

---

select year，wm_concat(dept) from table_a group by year;

---

select year,GROUP_CONCAT(dept Separator ',') from tablex group by year

注意分隔符默认是“,”，指定分隔符用Separator