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