MySQL group rows info columns based on column values within one table

Question

I would like to ask for help. I'm trying to combine multiple rows into multiple rows-columns. Here's the table a want to group:

+----+------+--------+--------+-------+ | id | type | index1 | index2 | value | +----+------+--------+--------+-------+ | 33 | v1 | 1 | 8 | 1a | | 33 | v1 | 2 | 6 | 1b | | 33 | v1 | 3 | 3 | 1c | | 33 | v2 | | 3 | 2x | | 33 | v2 | | 8 | 2y | | 33 | v2 | | 6 | 2z | | 34 | v1 | 1 | 5 | 1ass | | 34 | v1 | 2 | 3 | 1bss | | 34 | v1 | 3 | 4 | 1css | | 34 | v2 | | 3 | 2xss | | 34 | v2 | | 4 | 2yss | | 34 | v2 | | 5 | 2zss | +----+------+--------+--------+-------+

Index1 is used to keep order of index2 and to connect it with v2 type values.

This is how the result should look like:

+----+--------+--------+------+------+ | id | index1 | index2 | v1 | v2 | +----+--------+--------+------+------+ | 33 | 1 | 8 | 1a | 2y | | 33 | 2 | 6 | 1b | 2z | | 33 | 3 | 3 | 1c | 2x | | 34 | 1 | 5 | 1ass | 2zss | | 34 | 2 | 3 | 1bss | 2xss | | 34 | 3 | 4 | 1css | 2yss | +----+--------+--------+------+------+

Thanks, T.

Possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Arnold Daniels, Jan 30 '17 at 18:47

score 0 · Accepted Answer · answered Jan 30 '17 at 18:27

0

Is this you want:

select
    t1.id,
    t1.index1,
    t1.index2, 
    t1.value v1
    t2.value v2
from (
    select *
    from your_table
    where type = 'v1'
) t1 left join (
    select *
    from your_table
    where type = 'v2'
) t2 on t1.id = t2.id
and t1.index2 = t2.index2;

answered Jan 30 '17 at 18:27

Gurwinder Singh

38,557
6
51
76

Ok got it :) I had incorrect values in my table. Just added additional JOINs and it works like intended. THANKS! – Mr.Tony Jan 30 '17 at 21:39

MySQL group rows info columns based on column values within one table

1 Answers1