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

Question

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

zealous · Accepted Answer · 2020-05-10 22:46:01Z

3

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 |

edited May 10, 2020 at 22:46

answered May 10, 2020 at 22:39

zealous

7,5434 gold badges20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joe Doe Over a year ago

is this the only way to do it?

zealous Over a year ago

@JoeDoe cannot think of any other option but this would be the ideal way to achieve your output. :)

Gordon Linoff Over a year ago

@JoeDoe . . . This is the simplest way to do this. I can imagine other methods, but this is the right way.

Gordon Linoff · Accepted Answer · 2020-05-11 01:37:39Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related