CODEWITHSUNDEEP
mysqlsql
Shannin
Shannin

Reputation: 35

How can I do a GROUP_CONCAT in MySQL without an aggregate function?

I'm working on mysql workbench and working on one requirement.

Here is my table data

ID  Name
1   A
1   B
2   C
2   D
3   E
3   F

Expected Output

ID  Name
1   A,B
2   C,D
3   E,F

I've already got my output using GROUP_CONCAT function. But I want to solve this problem without using any function like these.

I know its possible but not able to get any turn to design logic to overcome this query.

Any suggestions please ?

Thanks in Advance !!

Upvotes: 1

Views: 231

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44961

select      id
           ,max(names) as names

from       (select       @prev_id   := @id                                                                          as previous_id
                        ,@id        := t.id                                                                         as id
                        ,@name      := case t.id when @prev_id then concat_ws(',',@name,t.name) else t.name end     as names

                        ,name

            from        t

            order by    t.id
                       ,t.name
            ) t

group by    id
;

Upvotes: 1

Related Questions