MySQL格式化查询-将列结果设置为表头

Question

我正在尝试在 MySQL 中创建一个报告，以按客户和市场输出交易股票的数量。

我得到了我想要的结果(使用多个表)，但需要更改输出表，使一列的结果成为另一列的标题，然后按市场获得客户的数量。

这就是我目前所拥有的；

mysql> SELECT IFNULL(sc.name,"All Clients") as "Clients",
 IFNULL(o.exchangeid,"All Markets") as "Markets",  
SUM(o.filledqty) as "Total Shares"
from ixrisk.orders o, ixrisk.clients c, ixrisk.sub_clients sc
where c.sub_client_id = sc.id and o.compid = c.clientname
GROUP BY sc.name, o.exchangeid WITH ROLLUP;
+------------------+-------------+--------------+
| Clients          | Markets     | Total Shares |
+------------------+-------------+--------------+
| FIXN_CL_CLIENT_1 | AMS         |           70 |
| FIXN_CL_CLIENT_1 | BTE         |          112 |
| FIXN_CL_CLIENT_1 | LSE         |         1147 |
| FIXN_CL_CLIENT_1 | All Markets |         1329 |
| qa_client_tst1   | LSE         |            0 |
| qa_client_tst1   | All Markets |            0 |
| All Clients      | All Markets |         1329 |
+------------------+-------------+--------------+
7 rows in set (0.01 sec)

我希望它能像这样出来，但做不到。

+------------------+------+------+------+-------------+
| Clients          | AMS  | BTE  | LSE  | All Markets |
+------------------+------+------+------+-------------+
| FIXN_CL_CLIENT_1 | 70   | 112  | 1147 |  1329       |
| qa_client_tst1   | 0    | 0    | 0    |  0          |
| All Clients      | 70   | 112  | 1147 |  1329       |
+------------------+------+------+------+-------------+
3 rows in set (0.01 sec)

我查看了类似的主题，并尝试了“内部联接”和“枢轴”，但无法让它们与我的其他参数一起使用。

谁能提供任何帮助？

更新我已经修改了 MySQL 脚本以获得我现在想要的格式，但需要让每个客户端只有一行包含所有 Exchange 值。我已经尝试了各种组合并且可以获得单行，但是值没有正确填充。

Answer 1

    mysql> select sc.name,
COALESCE(SUM(case when `exchangeid` = 'AMS' then o.filledqty end),0) as AMS,
COALESCE(SUM(case when `exchangeid` = 'BRU' then o.filledqty end),0) as BRU,
COALESCE(SUM(case when `exchangeid` = 'BTE' then o.filledqty end),0) as BTE,
COALESCE(SUM(case when `exchangeid` = 'CHI' then o.filledqty end),0) as CHI,
COALESCE(SUM(case when `exchangeid` = 'CPH' then o.filledqty end),0) as CPH,
COALESCE(SUM(case when `exchangeid` = 'GER' then o.filledqty end),0) as GER,
COALESCE(SUM(case when `exchangeid` = 'HEX' then o.filledqty end),0) as HEX,
COALESCE(SUM(case when `exchangeid` = 'JNB' then o.filledqty end),0) as JNB,
COALESCE(SUM(case when `exchangeid` = 'LIS' then o.filledqty end),0) as LIS,
COALESCE(SUM(case when `exchangeid` = 'LSE' then o.filledqty end),0) as LSE,
COALESCE(SUM(case when `exchangeid` = 'MCE' then o.filledqty end),0) as MCE,
COALESCE(SUM(case when `exchangeid` = 'MIL' then o.filledqty end),0) as MIL,
COALESCE(SUM(case when `exchangeid` = 'OSL' then o.filledqty end),0) as OSL,
COALESCE(SUM(case when `exchangeid` = 'PAR' then o.filledqty end),0) as PAR,
COALESCE(SUM(case when `exchangeid` = 'STO' then o.filledqty end),0) as STO,
COALESCE(SUM(case when `exchangeid` = 'SWX' then o.filledqty end),0) as SWX,
COALESCE(SUM(case when `exchangeid` = 'TRQ' then o.filledqty end),0) as TRQ,
COALESCE(SUM(case when `exchangeid` = 'VIE' then o.filledqty end),0) as VIE,
COALESCE(SUM(case when `exchangeid` = 'VTX' then o.filledqty end),0) as VTX,
COALESCE(SUM(o.filledqty),0) as "All Markets"
from orders o
INNER JOIN clients c on o.compid = c.clientname
INNER JOIN sub_clients sc on c.sub_client_id = sc.id
INNER JOIN sub_clients sc on c.sub_client_id = sc.id
group by sc.name;