CODEWITHSUNDEEP
mysqlsql
Joe Doe
Joe Doe

Reputation: 573

how do i group rows by an id row in group_concat into one row string?

i have table like this

|  col1  |  col2  |  col3  |
|  a     |  1     |  g1    |
|  b     |  2     |  g1    |
|  c     |  3     |  g1    |
|  d     |  4     |  g2    |
|  e     |  5     |  g2    |
|  f     |  6     |  g2    |

i want to select them group by col3 column to get this result

g1: a1, b2, c3-g2: d4, e5, f6

i used

SELECT
  CONCAT(col3,":",GROUP_CONCAT(col1, col2))
FROM table
GROUP BY col3

to get rows like this

--------------
g1:a1,b2,c3
--------------
g2:d4,e5,f6
--------------

but i want to merge them into one string separated by dashes like this

g1:a1,b2,c3-g2:d4,e5,f6

Upvotes: 0

Views: 225

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

You can do this without a subquery. But the logic is rather complicated -- using json functions, window functions, and fixing up strings:

select col3, group_concat(col1),
       replace(replace(replace(json_unquote(json_arrayagg(concat(col3, ':', group_concat(col1))) over (order by col3)),
                               '["', ''
                              ), '"]', ''
                      ), '", "', '-'
              )
from t
group by col3
order by col3 desc
limit 1;

JSON is needed in order to "aggregate" the strings using a window function, to avoid the subquery. The logic would be a bit simpler if MySQL supported group_concat() (or the standard listagg()) as a window function.

Here is a db<>fiddle.

For instance, this is what the logic looks like in Postgres, which does support string aggregation as a window function.

Upvotes: 2

zealous
zealous

Reputation: 7503

Try the following, you can use another GROUP_CONCAT. Here is the demo.

select
  GROUP_CONCAT(ncol separator '-') as merged_rows
from
(
  SELECT
    CONCAT(col3,":",GROUP_CONCAT(col1, col2)) as ncol
  FROM table
  GROUP BY col3
) val

Output:

| merged_rows             |
| ----------------------- |
| g1:a1,b2,c3-g2:d4,e5,f6 |

Upvotes: 3

Related Questions