GROUP_CONCAT for multiple columns

Question

Table:

CREATE TABLE cities  
(
    `year` INTEGER,
    `job` TEXT,
    `well` TEXT
);

INSERT INTO cities (`year`, `job`, `well`)
VALUES ('2001', 'A', 'G1'),
       ('2001', 'S', 'G1'),
       ('2001', 'S', 'G2'),
       ('2002', 'S', 'G1'),
       ('2008', 'S', 'G2');

SELECT year, GROUP_CONCAT( job || '-' || well, ',  ') AS id_city
FROM cities
GROUP BY year;

I get:

year    id_city
--------------------------
2001    A-G1,  S-G1,  S-G2
2002    S-G1
2008    S-G2

I want:

year   job   well
---------------------
2001    A     G1
2001    S     G1,G2
2002    S     G1
2008    S     G2

Answer 1

You should group by job also and use only well in GROUP_CONCAT():

SELECT year, 
       job,
       GROUP_CONCAT(well, ', ') AS id_city 
FROM cities 
GROUP BY year, job;

See the demo.