将mysql查询行转置为列 [英] Transpose mysql query rows into columns

问题描述

我有一个简单的查询，产生以下结果:

I have a simple query that produces the below results:

SELECT month,transporttype,count(transporttype) as loads 
from deliveries 
group by month,transporttype

我想将行转置为列.

我了解mysql没有枢轴功能，因此需要工会，但不能100％确定.

I understand mysql does not have pivot functions so a union is required but not 100% sure.

预先感谢您的帮助.

推荐答案

您可以使用像这样的交叉表-

You can do it with a crosstab like this -

SELECT
    `year`,
    `month`,
    SUM(IF(`transporttype` = 'inbound',                 1, 0)) AS `inbound`,
    SUM(IF(`transporttype` = 'LocalPMB',                1, 0)) AS `LocalPMB`,
    SUM(IF(`transporttype` = 'Long Distance',           1, 0)) AS `Long Distance`,
    SUM(IF(`transporttype` = 'shuttle',                 1, 0)) AS `shuttle`,
    SUM(IF(`transporttype` = 'export',                  1, 0)) AS `export`,
    SUM(IF(`transporttype` = 'Extrusions-LongDistance', 1, 0)) AS `Extrusions-LongDistance`,
    SUM(IF(`transporttype` = 'Extrusions-Shuttle',      1, 0)) AS `Extrusions-Shuttle`
FROM `deliveries`
GROUP BY `year`, `month`

另外，您应该将transporttype值移动到查找表中，并在该表中包含transporttype_id.

On a different note, you should move transporttype values to a lookup table and have transporttype_id in this table.

这篇关于将mysql查询行转置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！