i have table like this
x
| 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
Advertisement
Answer
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 |