将多行转换为列

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`