concat different values into one column

Question

Blockquote

I Hello Community,

I got a Problem which I wasn´t able to find a solution. It´s a problem from my work, but I changed it to explain it better.

I have some names in a table and in another I have different cities. The names are conected to the city with their ID.

Now I want to know, who lives in the city without show the city multiple times. It is like aggregating or concate the values (varchar) in one row for each city.

First I created the tables:

drop table if exists question;
CREATE TABLE question (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    Last_Name VARCHAR(30),
    First_Name VARCHAR(30),
    Birthday VARCHAR(30)
);

insert into question (Last_Name,First_Name,Birthday)
    values  ('Mueller','Max','01.09.1989')
            ,('Doe','Jane','02.09.1989')
            ,('Musterman','Heike','01.09.1989')
            ,('Perez','Juliana','03.09.1989')
            ,('Mezu','Maria Fernanda','04.09.1989')
            ,('Delgado','Luis','02.09.1989')
            ,('McFarmer','Madleine','01.09.1989')
            ,('Estrella','Anna','03.09.1989')
            ,('Flores','Eleazar','04.09.1989');

drop table if exists cuidad;

CREATE TABLE cuidad (
    cuidad_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    city VARCHAR(30),
    hab_id INT
);

insert into cuidad (hab_id,city)
    values  ('1','Frankfurt')
            ,('2','New York')
            ,('3','Frankfurt')
            ,('4','Santiago de Cali')
            ,('5','Pereira')
            ,('6','Santiago de Cali')
            ,('7','New York')
            ,('8','Pereira')
            ,('9','Santiago de Cali');

Now I want something like this:

Santiago de Cali | Perez - Delgado - Flores

I tried it with this code:

SELECT
    c.city
    ,CASE WHEN
                LEAD(q.Last_Name,1) OVER(PARTITION BY c.city ORDER BY id ASC) IS NOT NULL THEN
                concat(q.Last_Name,' - ' ,LEAD(q.Last_Name,1) OVER(PARTITION BY c.city ORDER BY id ASC))
                End as Name_aggr

FROM question q LEFT JOIN cuidad c
    ON q.id=c.hab_id;

But it doesn´t work well. I got two problems

1. For each row it only concate two citizens
2. It repeats it with every citizien, so I got 'A - B', 'B - C', NULL

Here is the output:

Output

It doesn´t matter if it is in one row o it is in a few row with pivoting.

Many thanks in advance.

Answer 1

I modified your code and redesigned table schemes. Take a look how people connect to the cities now. You don't need multiple rows with the same city name. Also you shouldn't use two languages at the same time (spanish and english) and consider changing your birthday date format (to YYYY-MM-DD).

I used GROUP_CONCAT to display result you want to get.

Schema (MySQL v5.7)

CREATE TABLE question (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    last_name VARCHAR(30),
    first_name VARCHAR(30),
    birthday VARCHAR(30),
    ciudad_id INT
);

insert into question (last_name, first_name, birthday, ciudad_id)
    values  ('Mueller','Max','01.09.1989', 1)
            ,('Doe','Jane','02.09.1989', 2)
            ,('Musterman','Heike','01.09.1989', 1)
            ,('Perez','Juliana','03.09.1989', 3)
            ,('Mezu','Maria Fernanda','04.09.1989', 4)
            ,('Delgado','Luis','02.09.1989', 3)
            ,('McFarmer','Madleine','01.09.1989', 2)
            ,('Estrella','Anna','03.09.1989', 4)
            ,('Flores','Eleazar','04.09.1989', 3);

CREATE TABLE ciudad (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    name VARCHAR(30)
);

insert into ciudad (id, name)
    values  (1,'Frankfurt')
            ,(2,'New York')
            ,(3,'Santiago de Cali')
            ,(4,'Pereira');

---

Query #1

SELECT ciudad.name as city, GROUP_CONCAT(question.last_name separator ' - ') as people FROM question LEFT JOIN ciudad ON ciudad.id=question.ciudad_id GROUP BY ciudad.name;

Output:

| city             | people                   |
| ---------------- | ------------------------ |
| Frankfurt        | Mueller - Musterman      |
| New York         | McFarmer - Doe           |
| Pereira          | Mezu - Estrella          |
| Santiago de Cali | Perez - Delgado - Flores |

View on DB Fiddle

Answer 2

We can use group_concat for what you need,

select c.city,group_concat( q.last_name separator '-') strg_agg from cuidad c
left join question q
  on q.id = c.hab_id
group by c.city;

I took the cuidad table as driving table instead of question which should not be a problem in this case.