sql 行转列，总结分析

合并行 ，行转列

SELECT * FROM test.`dd`;

output:
id  verdor_id       x       y       z  date
------  ---------  ------  ------  ------  ------------
 1  A              11     789      24  2016-06-23
 2  B               1      11      28  2016-06-23
 3  B               1      11      28  2016-06-22
 4  B               1      11      28  2016-06-21
 5  A              11     789      24  2016-06-22

# 合并行 ，行转列
SELECT
dd.`date`,
MAX(CASE WHEN verdor_id = 'A' THEN dd.`x` ELSE 0 END) 'A_vendor_x',
MAX(CASE WHEN verdor_id = 'B' THEN dd.`x` ELSE 0 END) 'B_verdor_x',
MAX(CASE WHEN verdor_id = 'A' THEN dd.`y` ELSE 0 END) 'A_vendor_y',
MAX(CASE WHEN verdor_id = 'B' THEN dd.`y` ELSE 0 END) 'B_verdor_y',
MAX(CASE WHEN verdor_id = 'A' THEN dd.`z` ELSE 0 END) 'A_vendor_z',
MAX(CASE WHEN verdor_id = 'B' THEN dd.`z` ELSE 0 END) 'B_verdor_z'
FROM `dd`
GROUP BY dd.`date`

output:
date        A_vendor_x  B_verdor_x  A_vendor_y  B_verdor_y  A_vendor_z  B_verdor_z
----------  ----------  ----------  ----------  ----------  ----------  ------------
2016-06-21           0           1           0          11           0            28
2016-06-22          11           1         789          11          24            28
2016-06-23          11           1         789          11          24            28

如何理解上面这条 sql 是如何工作的？

首先：

SELECT
dd.`date`,
CASE WHEN verdor_id = 'A' THEN dd.`x` ELSE 0 END 'A_vendor_x',
CASE WHEN verdor_id = 'B' THEN dd.`x` ELSE 0 END 'B_verdor_x',
CASE WHEN verdor_id = 'A' THEN dd.`y` ELSE 0 END 'A_vendor_y',
CASE WHEN verdor_id = 'B' THEN dd.`y` ELSE 0 END 'B_verdor_y',
CASE WHEN verdor_id = 'A' THEN dd.`z` ELSE 0 END 'A_vendor_z',
CASE WHEN verdor_id = 'B' THEN dd.`z` ELSE 0 END 'B_verdor_z'
FROM `dd`

output:
date        A_vendor_x  B_verdor_x  A_vendor_y  B_verdor_y  A_vendor_z  B_verdor_z
----------  ----------  ----------  ----------  ----------  ----------  ------------
2016-06-23          11           0         789           0          24             0
2016-06-23           0           1           0          11           0            28
2016-06-22           0           1           0          11           0            28
2016-06-21           0           1           0          11           0            28
2016-06-22          11           0         789           0          24             0

然后，通过 max + group by 去掉 0 即当初未匹配到的情况。