Question: I have a set of 6 variables for observation representing the Brand of tires in a truck. The brands could be different between tires but usually are all the same.
I need to create a summary variable that lists for each observation all the brands of the tires.

A simple `SELECT CONCAT(BrandP1, ', ', BrandP2...) AS Brand` usually gives a lot of repetitions.

I thought of concatenating the N variable only if the N-1 variables are different but i see it's quite ugly doing so. Is there an elegant way to summarize the content of 6 variables into one new calculated variable?

As requested an extract of the table:

```
ID_vehicle, BrandP1, BrandP2, BrandP3, BrandP4
----------------------------------------------
1    Dunlop    Dunlop    Riken    Riken
2    Dunlop    Dunlop    Dunlop   Dunlop
3    Riken     Dunlop    Toyo     Toyo

```

What I'd like to obtain is 

```
ID_vehicle, Brand
------------------
1   Dunlop, Riken
2   Dunlop
3   Riken, Dunlop, Toyo

```

What I can get with `CONCAT` is

```
1   Dunlop, Dunlop, Riken, Riken
2   Dunlop, Dunlop, Dunlop, Dunlop
3   Riken, Dunlop, Toyo, Toyo

```

Answer: 
A more elegant solution might be to redesign the "brands" table with your brand and tire position as separate columns. Then you could use a `DISTINCT` `GROUP_CONCAT` to list the brands found/used during each observation. Something like: 

```
SELECT ALL
    `observation_id`,
    GROUP_CONCAT( DISTINCT `tire_brand` SEPARATOR ', ')   
FROM
    `truck_observations`
GROUP BY
    `observation_id`

```

That way observations similar to:

```
ID, Position, Brand
----------------------
1, 1, Dunlop
1, 2, Dunlop
1, 3, Dunlop
1, 4, Toyo
1, 5, Riken
1, 6, Riken
2, 1, Riken
2, 2, Dunlop
2, 3, Dunlop
2, 4, Riken
2, 5, Dunlop
2, 6, Riken

```

Can be summarized as:

```
ID, Brands
----------------------
1, (Dunlop, Toyo, Riken)
2, (Riken, Dunlop)

```
I figured it out, in part thanks to @a3r0, although I couldn't create a new table or changing the database schema.

So I went

```
SELECT ALL
    ID,
    GROUP_CONCAT( DISTINCT Brand SEPARATOR ', ') AS BrandP
FROM (
    SELECT ID, Wheel, Brand FROM (
        SELECT ID, 'BrandP1' Wheel, BrandP1 Brand FROM Pneumatici
        UNION ALL
        SELECT ID, 'BrandP2', BrandP2 FROM Cars
        UNION ALL
        SELECT ID, 'BrandP3', BrandP3 FROM Cars
        UNION ALL
        SELECT ID, 'BrandP4', BrandP4 FROM Cars
        UNION ALL
        SELECT ID, 'BrandP5', BrandP5 FROM Cars
        UNION ALL
        SELECT ID, 'BrandP6', BrandP6 FROM Cars
    ) AS y
    WHERE Brand != ''
) AS x
GROUP BY ID

```

To transpose the variables I'm interested with
and then joined it with the original table matching by id.