SQL 将某字段拼接后，再和其他表进行联合查询
比如，表T1，有Id,Name,Sex，并有如下记录：
Id Name
1 张
1 三
1 丰
2 陈
2 晓
2 薇

希望查出结果如下：
Id Name_full
1 张三丰
2 陈晓薇


然后关联到其他表进行查询。这段SQL怎么写？求大虾门教教。
搜了很多例子好像都不怎么适用。。

[解决办法]

SQL code

--> 测试数据：[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 ALLSELECT 1,'三' UNION ALLSELECT 1,'丰' UNION ALLSELECT 2,'陈' UNION ALLSELECT 2,'晓' UNION ALLSELECT 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 ALLSELECT 1,'三' UNION ALLSELECT 1,'丰' UNION ALLSELECT 2,'陈' UNION ALLSELECT 2,'晓' UNION ALLSELECT 2,'薇'    SELECT DISTINCT [Id],    [Name]=(select[Name]+'' FROM [tb] WHERE  [Id] =t.[Id] FOR XML PATH(''))    FROM [tb] AS t/*Id    Name1    张三丰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)goinsert into [T1]select 1,N'张' union allselect 1,N'三' union allselect 1,N'丰' union allselect 2,N'陈' union allselect 2,N'晓' union allselect 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)*/