--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([name] varchar(4),[address] varchar(8))
insert [tbl]
select '张三','北京' union all
select '李四','河北' union all
select '王五','河南' union all
select '张三','朝阳区' union all
select '张三','阳光大街' union all
select '李四','沧州'



select * into #tt from(
SELECT *FROM (SELECT DISTINCT [name] FROM [tbl])A
OUTER APPLY(
    SELECT [address]= STUFF(REPLACE(REPLACE(
            (   SELECT [address] FROM [tbl] N
                WHERE [name] = A.[name]
                FOR XML AUTO
             ), '<N address="', ' '), '"/>', ''), 1, 1, '')
)N)a
select * from #tt
/*
name    address
李四    河北 沧州
王五    河南
张三    北京 朝阳区 阳光大街
*/