父表ID查询子表 所属name字符拼接 for xml path

Declare@tTable 

(

IDint,

FNamevarchar(20),

CNamevarchar(20),

CAgevarchar(20)

)

InsertInto@t

select 1,'吴亮','吴1','3岁'Union

select 1,'吴亮','吴2','5岁'Union

select 1,'吴亮','吴3','9岁'Union

select 4,'麦大伟','麦1','3岁'Union

select 4,'麦大伟','麦2','5岁'Union

select 6,'张思','张1','3岁'

 

SelectID,FName,CName=Stuff((Select'，'+CNameFrom@tWhereID=T.IDForxmlpath('')),1,1,''),

CAge=Stuff((Select'，'+CAgeFrom@tWhereID=T.IDForxmlpath('')),1,1,'')

From@tTGroupbyid,FName

 

 

 

 

 

select id,name,Ctypeid,ISNULL(STUFF((select ','+name from A00006 where Id in(select Aid from A00010 where Bid=c.Id) for xml path('')) ,1,1,'' ),'') temp  from A00009 c