mysql从表中用case,如何使用case语句在MySQL中透视表？

I am trying to pivot a table in MySQL using case statements. This question has been asked many times here, and I have studied all of those answers, but I am looking for a solution that:

1. Uses case statements. Not self joins, subqueries, or unions.

2. Uses just SQL. Not Excel or shell scripts.

3. Works on MySQL.

Here is the table:

create table client (

name varchar(10),

revenue int(11),

expense int(11)

);

insert into client (name, revenue, expense) values ("Joe", 100, 200);

insert into client (name, revenue, expense) values ("Bill", 300, 400);

insert into client (name, revenue, expense) values ("Tim", 500, 600);

mysql> select * from client;

+------+---------+---------+

| name | revenue | expense |

+------+---------+---------+

| Joe | 100 | 200 |

| Bill | 300 | 400 |

| Tim | 500 | 600 |

+------+---------+---------+

I would like to pivot the table to this:

+-----+------+-----+

| Joe | Bill | Tim |

| 100 | 300 | 500 |

| 200 | 400 | 600 |

+-----+------+-----+

How can I accomplish this?

I have already seen the solutions at artfulsoftware dot com and buysql dot com, but those solutions are not working for my table.

解决方案select

sum(case when name='Joe' then revenue else 0 end) as JOE,

sum(case when name='Bill' then revenue else 0 end) as Bill,

sum(case when name='Tim' then revenue else 0 end) as TIM

from client

union

select

sum(case when name='Joe' then expense else 0 end) as JOE,

sum(case when name='Bill' then expense else 0 end) as Bill,

sum(case when name='Tim' then expense else 0 end) as TIM

from client