Fetching values from a table without using CURSORS

Question

My table has the following structure

ID  MName  FName
1   Sunil Sachin
2   Sunil Sanjay
3   Sunil Wasim
4   Greg  Ricky
5   Ian   Mark

I want the query to return

1 Sunil Sachin, Sanjay, Wasim
2 Sunil Sachin, Sanjay, Wasim
3 Sunil Sachin, Sanjay, Wasim
4 Greg Ricky
5 Ian Mark

Tommietommy

Why do you need a cursor in the first place? – Sakai

Why repeat the first row thrice? – Instantaneity

The data is already in production. Need to generate a report in SQL Server. I know how to get it using a CURSOR and COALESCE. But it is costly. Is there an elegant way to do the same? – Tommietommy

what exactly is your query doing? i mean what is the logic here? can you post your solution using CURSOR – Pizor

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 – Tommietommy

Answer 1

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

Hypsography

Thanks. It works. Also explainextended.com/2010/06/21/group_concat-in-sql-server is a very good link. – Tommietommy

Hi,I have a question. What does pre_trimmed (names) do. I used any other string and it works too. – Tommietommy

@Priyaranjan, pre-trimmed specifies the table alias to use for the derived table, and (names) is the column alias. Since pre-trimmed isn't used anywhere in the query, you can change it whatever you'd like (t, foo, etc.). You can also change the column alias, but you'll have to update the rest of the query if you do. – Hypsography

Yeah, understood it on second thought. Thanks a lot. – Tommietommy