有两个表:主表user 从表info
User数据id
1
2 info表数据id name
1 nick
1 kevin
1 brain
2 westlife
2 nsyncuser表的id和info表的id是主外键关系
现在我想要这样的查询结果id name
1 nick,kevin,brain
2 westlife,nsync就是把id相同的的name累加起来,并用逗号隔开
麻烦各位高手了谢谢!!!!!!!!!
User数据id
1
2 info表数据id name
1 nick
1 kevin
1 brain
2 westlife
2 nsyncuser表的id和info表的id是主外键关系
现在我想要这样的查询结果id name
1 nick,kevin,brain
2 westlife,nsync就是把id相同的的name累加起来,并用逗号隔开
麻烦各位高手了谢谢!!!!!!!!!
select u.ID,(stuff((select ','+[name] from Info where ID=u.ID),1,1'')) as [name]
from [user] u
from info
where name=a.name for xml path('')))
from info a
group by id
select id,name=STUFF((select ','+name
from info
where name=a.name for xml path('')),1,1,'')
from info a
group by id
select * into #temp from info order by id,namedeclare @id int,@name varchar(2000)
select @id=0,@name=''update #temp set @name=name=case when @id=id then @name+','+name else name end,@id=idselect id,max(@name) as name from #temp group by id
select u.ID,(stuff((select ','+[name] from Info where ID=u.ID for xml path('')),1,1,'')) as [name]
from [user] u