2

is it possible to have numbering in GROUP_CONCAT

like

If, from GROUP_CONCAT(empnam SEPARATOR ', ')

I get a set,

 < JohnM, DannyP, TiffnyK, KarlM >

I need to have 

 < 1.JohnM, 2.DannyP, 3.TiffnyK, 4.KarlM >

I tried following, but didnt get desired results.

    SET @x:=0;

    SELECT 

            GROUP_CONCAT(@x:=@x+1,' ', s.empnam SEPARATOR ',   ') AS emps,  @x:=0 
    < tables >
    < filters >

is it possible at Query-Level, or I have to do it at Application Side ?

Hytool
  • 1,358
  • 1
  • 7
  • 22

2 Answers2

3

Years later, we should abandon mutating variables inside a select statement, as since MySQL 8 we can use the standard way, with window functions:

with base as (
      select   dep, 
               empnam,
               count(*) over (partition by dep order by empnam) num
      from     t)
select   dep,
         group_concat(concat(num, '.', empnam) separator ', ') emps
from     base
group by dep

See db-fiddle

Original answer (2016)

You can do this on the application side, but in MySQL 5.7 it is possible. In the following query, I assume you group the names by something, for example their department (I called it dep). This in order to illustrate that the counter starts from 1 for every new group.

select   dep, 
         group_concat( 
             concat(@i := if (@grp = dep, @i + 1, if(@grp := dep,1,1)), '.', empnam) 
             separator ', ') emps
from     t,
         (select @i := 0, @grp := '') init
group by dep;

See SQL fiddle or db-fiddle.

Make sure to put your table name in the from clause, and to use the actual field you want to group by. If you have multiple fields to group by, the expression assigned to @i will need to change. You could for instance concatenate the values that define a group.

By using a separator of two characters you ensure to have a space between each name.

trincot
  • 317,000
  • 35
  • 244
  • 286
  • I knew this is quite old but do you know what could be the course why i don't get the same result in your fiddle? e.g. https://www.db-fiddle.com/f/x4diVXc2WNeycuv3CShPpB/0 it isn't the mysql version which can easily be checked here https://dbfiddle.uk/?rdbms=mysql_5.7&rdbms2=mysql_5.6&fiddle=8d2ff918b2f5a8e87726c36920c48bd8 – BHoft Oct 12 '20 at 12:16
  • Indeed, an old Q&A. I have no idea why the result is different on db-fiddle: it looks like variables are evaluated at a different moment. Anyway, since MySQL 8, with its window function support, there should be no more reason to use mutating variables in a query. The behaviour of this variable mechanism is undefined and should not be used any more. – trincot Oct 13 '20 at 09:58
  • 1
    I have updated my answer, including the MySQL 8 way to do it. – trincot Oct 13 '20 at 10:12
  • thank you very much for the mysql 8 solution, sadly i also to support older versions. still figure out a way to get the result what i want. – BHoft Oct 13 '20 at 11:17
  • OK, I played a bit, and for some reason the db engine behind db-fiddle doesn't like that variables are not initialised. So add an initialisation in the `from` clause. I updated my answer accordingly. – trincot Oct 13 '20 at 12:08
  • I really struggle with getting a result with 2 fields to group by. Could you maybe give a simple example? I have made 2 fiddle one with single group which works https://www.db-fiddle.com/f/x4diVXc2WNeycuv3CShPpB/3 and another one with 2 grouped columns which doesn't work https://www.db-fiddle.com/f/3woAVSw5hrav15jAmuWVdT/1 i want to have a concatenated string of each review detail grouped by submission and category. And the text should be begin with 'Reviewer 1:', 'Reviewer 2:' depending on which submission was first, second etc. – BHoft Oct 13 '20 at 15:12
  • This really goes beyond this Q&A. Could you ask a new question about it? – trincot Oct 13 '20 at 15:30
1

Try this:

SET @x:=0;

SELECT 
        GROUP_CONCAT(CONCAT(@x:=@x+1, '.', s.empnam) SEPARATOR ',   ') AS emps,  @x:=0 
< tables >
< filters >
Blank
  • 12,308
  • 1
  • 14
  • 32
  • not really diff then my query's result, it doesn't get refresh at 1 on new record , anyway ! I did it at Application side – Hytool Jul 19 '16 at 06:07
  • 1
    @Hytool I think this should work, please check demo here http://sqlfiddle.com/#!9/4b523/3 – Blank Jul 19 '16 at 07:52
  • As my query had to do loads of GROUP BYs for single record, It was mess up within, anyway, your solution works with simply group by query, so works my solution as well ! thank you for your time ! – Hytool Jul 19 '16 at 11:19