SQL Merge two rows with same ID but different column values

Question

I am trying to merge different rows into one when they have the same id but different column values.

    (table1)

    id       colour

    1        red
    1        blue
    2        green
    2        red

I would like this to be combine so that the result is :

    id     colour1    colour2

    1      red        blue
    2      green      red

There is no limit for number of colors. It can range depending on the id.

Any help would be appreciated! Thanks in advance.

Why is colour1 = red and colour2 = blue and not the other way around ? SQL tables/resultsets are by ANSI SQL definition **orderless** — Raymond Nijland, Oct 22 '19 at 14:52
.. also with the current dataset it is also not possible you need to have `id |colour|position` -> `1|red|1`, `1|blue|2` .... — Raymond Nijland, Oct 22 '19 at 14:54
Possible duplicate of [MySQL dynamic pivot table](https://stackoverflow.com/questions/17773045/mysql-dynamic-pivot-table) — Raymond Nijland, Oct 22 '19 at 14:57

score 0 · Answer 1 · answered Oct 22 '19 at 17:00

This would work for your specific example

select left(colours,locate(',',colours)-1) as colour1 , right(colours, length(colours)- locate(',',colours)) as colour2 from (select id, group_concat(colour) as colours from table1 group by id) concatenation

not very general or robust.

If the set of values (colours) were fixed, I'd prefer to pivot it like this:

select id, sum(RED) as RED , sum(GREEN) as GREEN , sum(BLUE) as BLUE from (select id , colour='red' as RED , colour='green' as GREEN , colour='blue' as BLUE from table1) coded group by id;

but that wasn't the output format you requested.

SQL Merge two rows with same ID but different column values

1 Answers1