select code,name,
area=stuff((select '、'+area from tb where code=t.code and name=t.name for xml path('')),1,1,'')
from tb t
group by code,name

------解决方案--------------------

SQL code

--2005 or 2008
select code,name,
    stuff((select '、'+area from tb where code = t.code for xml path('')),1,1,'') area
from tb t
group by code,name

------解决方案--------------------

SQL code

create table xuyi
(code char(2), name char(4), area varchar(6))

insert into xuyi
select '01', '小明', '上海' union all
select '01', '小明', '北京' union all
select '01', '小明', '广州' union all
select '02', '小王', '上海' union all
select '02', '小王', '武汉'


with t as
(select a.code,a.name,
cast((select area+',' from xuyi b 
where b.code=a.code and b.name=a.name 
for xml path('')) as varchar(30)) area
from xuyi a
group by a.code,a.name
)
select code,name,left(area,len(area)-1) area
from t

code name area
---- ---- ------------------------------
01   小明   上海,北京,广州
02   小王   上海,武汉

(2 row(s) affected)