Search code examples
sqlsqlitegroup-bygroup-concat

How to do group_concat on two fields in sqlite?

My cities table:

year | id | city
2005   10   LA
2005   11   SF
2007   12   NY
2008   13   NO

with the query:

SELECT year, GROUP_CONCAT(id) 
FROM cities 
GROUP BY year

I get the output:

2005|10,11
2007|12
2008|13

But what I would like to get is something like (for example):

2005|10,LA,11,SF
2007|12,NY
2008|13,NO

In other words I would like both the id and the name in the result, grouped by year, and not just the id. How does one do that?

Solution

  • In SQLite GROUP_CONCAT() takes 2 arguments: the expression to be aggregated and the separator (the default separator is ,).
    In your case, you need multiple columns for the expression part so you must build it by concatenation:

    SELECT year, 
       GROUP_CONCAT(id || ',' || city) AS id_city
FROM cities
GROUP BY year;

    See the demo.