来自MySQL结果集的透视数据 - Pivot data from a MySQL result set

Pivot data from a MySQL result set

本文关键字：透视数据 MySQL 结果来自 | 更新日期: 2023-09-27

MySQL数据库结果集:

Department     Month      Value
Sale        February       50
Sale        March          35
Sale        April          65
Sale        May            120
Dispatch   February       85
Dispatch   March          23
Dispatch   April          45
Dispatch   May            33
.... etc

所有四个部门都是这样。

我希望PHP格式化数据结果，让它看起来像这样:

['Month',   'Sales',  'Dispatch',   'Support', 'Calibration'],
['February', 50,     85,         15,      53],
['March',    35,     23,         12,      55],

等

MySQL声明:

$sql_line_chart = "SELECT d.department as department, MONTHNAME(date_created) AS month, 
               COALESCE(SUM(CASE WHEN c.cat_id IN (5,6,7,8,9,10,11,12,13,15) THEN time_spent END), 0) as value
                FROM master AS m 
                 INNER JOIN category AS c ON c.cat_id = m.cat_id 
                  INNER JOIN department AS d ON d.dept_id = m.dept_id 
                  WHERE 
                  AND 
                  date_created > CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE) - 1 DAY 
                 AND  
                date_created < CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE) - 1 DAY + INTERVAL 1 YEAR 
               GROUP BY d.department, month 
              ORDER by d.department ASC, m.date_created ASC";

通过迭代查询结果来透视数据并建立一个新的2d数组。

为新行指定键是很重要的，否则，值可能被写在错误的列中。

代码(演示):

$columns = ['Sale', 'Dispatch', 'Support', 'Calibration'];
$columnLookup = array_flip($columns);
$result = [];
foreach ($array as $row) {
    $rowKey = $row['month'];
    $colKey = $columnLookup[$row['department']];
    $result[$rowKey][$colKey] = $row['value'];
}

一旦有了$result，将有许多方法将数据表示为HTML。下面是一个基本的/简单的方法来循环和输出没有HTML的数据:

foreach ($result as $month => $colVals) {
    printf(
        "%' 10s't%d't%d't%d't%d'n",
        $month,
        $colVals[0] ?? 0,
        $colVals[1] ?? 0,
        $colVals[2] ?? 0,
        $colVals[3] ?? 0
    );
}

输出:

  February  50  85  0   0
     March  35  23  0   0
     April  65  45  0   0
       May  120 33  0   0