在不使用游标的情况下从表中获取值 [英] Fetching values from a table without using CURSORS

问题描述

我的表具有以下结构

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

这篇关于在不使用游标的情况下从表中获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！