openpolitics.com

The power of MySQL’s GROUP_CONCAT

In the very early days of Percona Vadim wrote very nice post about GROUP_CONCAT.

mysql> SELECT DISTINCT

    -> parent_id, child_id

    -> FROM group_c

    -> ORDER BY parent_id;

+—————–+—————+

| parent_id | child_id |

+—————–+—————+

|         1 |        1 |

|         1 |        2 |

|         1 |        3 |

|         1 |        4 |

|         2 |        1 |

|         2 |        3 |

|         2 |        4 |

|         2 |        6 |

|         3 |        1 |

|         3 |        2 |

|         4 |        1 |

|         5 |        0 |

+—————–+—————+

12 rows in set (0.00 sec)

mysql> SELECT DISTINCT     -> parent_id, GROUP_CONCAT(DISTINCT child_id ORDER BY child_id) AS child_id_list     -> FROM group_c     -> group by parent_id     -> ORDER BY parent_id; +—————–+———————–+ | parent_id | child_id_list | +—————–+———————–+ |         1 | 1,2,3,4       | |         2 | 1,3,4,6       | |         3 | 1,2           | |         4 | 1             | |         5 | 0             | +—————–+———————–+ 5 rows in set (0.00 sec)