SQL GROUP_CONCAT WITH EDIT TEXT

Question

can sql make this?

Before                          After
+++++++++++++++++        +++++++++++++++++++++
+ ID |   ID2    +        + ID |    ID2       +
+++++++++++++++++        +++++++++++++++++++++
+ 1  |    A     +        + 1  |  A-1,B-2,C-1 +
+ 1  |    B     +        + 2  |  A-1,B-2     +
+ 2  |    A     +        + 3  |  C-1         +
+ 3  |    C     +        +++++++++++++++++++++
+ 1  |    B     +
+ 2  |    B     +
+ 1  |          +
+ 2  |    B     +
+ 2  |    C     +
+++++++++++++++++

Answer 1

You did not specify what database you are using but you referenced the MySQL GROUP_CONCAT function so this answer assumes you are using MySQL.

It appears that you want to do 2 things:

1. Generate a count of each ID/ID2 combination and concatenate those values together.
2. Group those pairs of ID2 and count into a single string based on the ID

The first step I would take would be to get the count for each id/id2 combo:

select id,
  id2,
  count(*) Total
from yourtable
group by id, id2

See Demo. Once you have this value, then you can use both the group_concat function and the concat function to create the final result:

select
  id,
  group_concat(concat(ID2, '-', Total) ORDER BY ID2 SEPARATOR ',') ID2
from
(
  select id,
    id2,
    count(*) Total
  from yourtable
  group by id, id2
) d
group by id;

See SQL Fiddle with Demo