本文介绍了在不使用游标的情况下从表中获取值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的表具有以下结构
ID MName FName
1 Sunil Sachin
2 Sunil Sanjay
3 Sunil Wasim
4 Greg Ricky
5 Ian Mark
我希望查询返回
1 Sunil Sachin, Sanjay, Wasim
2 Sunil Sachin, Sanjay, Wasim
3 Sunil Sachin, Sanjay, Wasim
4 Greg Ricky
5 Ian Mark
推荐答案
您可以使用执行 group_concat并获得所需的结果:
You can use this method to do a 'group_concat' and get the results you want:
with Data(ID, MName, FName) as
(
select 1, 'Sunil', 'Sachin'
union
select 2, 'Sunil', 'Sanjay'
union
select 3, 'Sunil', 'Wasim'
union
select 4, 'Greg', 'Ricky'
union
select 5, 'Ian', 'Mark'
)
select Data.ID, Data.MName, Names.FNames
from Data
join
(
select MName, left(names, len(names) - 1) as FNames
from Data as extern
cross apply (select FName + ', '
from Data as intern
where extern.MName = intern.MName
for xml path('')
) pre_trimmed (names)
group by MName, names
) Names ON Data.MName = Names.MName
order by Data.ID
这篇关于在不使用游标的情况下从表中获取值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!