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 ?
解决方案
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
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;
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.