mysql - 将多行转换为列

Question

我想将行更改为列。这就是我现在拥有的:

NAME      | YEAR | INCOME  | EXPENSE
COMPANY-1 | 2017 | €30000  | €15000
COMPANY-1 | 2016 | €50000  | €10000
COMPANY-2 | 2017 | €20000  | €5000
COMPANY-2 | 2016 | €80000  | €95000

这就是我想要的:

NAME      | INCOME_2017  | EXPENSE_2017 | INCOME_2016  | EXPENSE_2016
COMPANY-1 | €30000       | €15000       | €50000       | €10000
COMPANY-2 | €20000       | €5000        | €80000       | €95000

有人知道我该怎么做吗？我复制了很多 PIVOT 脚本。但这没有用...

Answer 1

您可以尝试此 sql 以获得所需的结果。

1) 使用 CASE 获取所需的列。

2) 使用最大条件获取最大值。

SELECT  `name` ,
 MAX( CASE WHEN  `year` =2017 THEN  `income` ELSE 0 END ) AS INCOME_2017,
 MAX( CASE WHEN  `year` =2017 THEN  `expense` ELSE 0 END ) AS EXPENSE_2017,
 MAX( CASE WHEN  `year` =2016 THEN  `income` ELSE 0 END ) AS INCOME_2016,
 MAX( CASE WHEN  `year` =2016  THEN  `expense` ELSE 0 END ) AS EXPENSE_2016
 FROM  `test_data` GROUP BY  `name`