mysql 怎样转换表,MySQL-将表转换为其他表

I'm probably not seeing things very clear at this moment, but I have a table in MySQL which looks like this:

ID | a | b | c

1 | a1 | b1 | c1

2 | a2 | b2 | c2

For some reason (actually a join on another table - based on ID, but I think if someone can help me out with this part, I can do the rest myself), I needed those rows to be like this instead:

1 | a1 | a

1 | b1 | b

1 | c1 | c

2 | a2 | a

2 | b2 | b

2 | c2 | c

So basically, I need to view the rows like: ID, columntitle, value

Is there any way to do this easily?

解决方案

You are trying to unpivot the data. MySQL does not have an unpivot function, so you will have to use a UNION ALL query to convert the columns into rows:

select id, 'a' col, a value

from yourtable

union all

select id, 'b' col, b value

from yourtable

union all

select id, 'c' col, c value

from yourtable

This can also be done using a CROSS JOIN:

select t.id,

c.col,

case c.col

when 'a' then a

when 'b' then b

when 'c' then c

end as data

from yourtable t

cross join

(

select 'a' as col

union all select 'b'

union all select 'c'

) c