--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([Id] INT,[Name] VARCHAR(2))
INSERT [tb]
SELECT 1,'张' UNION ALL
SELECT 1,'三' UNION ALL
SELECT 1,'丰' UNION ALL
SELECT 2,'陈' UNION ALL
SELECT 2,'晓' UNION ALL
SELECT 2,'薇'
--------------开始查询--------------------------

SELECT  *
FROM 
(
    SELECT DISTINCT [Id],
    (select[Name]+'' FROM [tb] WHERE  [Id] =t.[Id] FOR XML PATH('')) AS [Name]
    FROM [tb] AS t
) AS a 
JOIN .....

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

SQL code

IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([Id] INT,[Name] VARCHAR(2))
INSERT [tb]
SELECT 1,'张' UNION ALL
SELECT 1,'三' UNION ALL
SELECT 1,'丰' UNION ALL
SELECT 2,'陈' UNION ALL
SELECT 2,'晓' UNION ALL
SELECT 2,'薇'

    SELECT DISTINCT [Id],
    [Name]=(select[Name]+'' FROM [tb] WHERE  [Id] =t.[Id] FOR XML PATH(''))
    FROM [tb] AS t
/*
Id    Name
1    张三丰
2    陈晓薇
*/

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

SQL code
if object_id(N'[T1]') is not null drop table [T1]
create table [T1]([ID] int,[name] varchar(10) collate chinese_prc_ci_as)
go
insert into [T1]
select 1,N'张' union all
select 1,N'三' union all
select 1,N'丰' union all
select 2,N'陈' union all
select 2,N'晓' union all
select 2,N'薇'

select distinct ID,(SELECT [name]+'' FROM [T1] where t.[ID]=[ID] for xml path('')) 'name'
from [T1] t





/*

(6 row(s) affected)
ID          name
----------- ----------------------------------------------------------------------------------------------------------------
1           张三丰
2           陈晓薇

(2 row(s) affected)

*/