php - 如何使用 php 数组将 sql 查询结果转换为数据透视表？

Question

我正在尝试为图书馆预订系统创建一些统计数据。我的 sql 查询结果类似于以下结构。

total_no_students| department  | property       | month   241             | Physics     | undergraduate  | Nov 236             | Physics     | undergraduate  | Dec 254             | Physics     | postgraduate   | Nov 210             | Physics     | postgraduate   | Dec 193             | Architecture| undergraduate  | Nov 181             | Architecture| undergraduate  | Dec 127             | Architecture| postgraduate   | Nov 292             | Architecture| postgraduate   | Dec 134             | Biology     | undergraduate  | Nov 188             | Biology     | undergraduate  | Dec 129             | Biology     | postgraduate   | Nov 219             | Biology     | postgraduate   | Dec

我正在尝试使用 php 编写一些代码以创建具有以下外观的统计表:

    |Physics-undergrad|Physics-postgrad|Architecture-undergrad|Architecture-postgrad|Nov |      241        |     254        |         193          |        127          |Dec |      236        |     210        |         181          |        292          |

我必须使用数组和一些循环 (foreach) 使用 php(枢轴技术)对其进行转换。我尝试这样做，但我对嵌套循环感到困惑。

我写的代码如下:

$csvArray = array();$departments = array('Physics','Architecture','Biology');$properties = array('undergraduate','postgraduate');$con = mysqli_connect($mysql_hostname, $mysql_user, $mysql_password, $mysql_dbname);$sql="SELECT count(id) as total_no_students, department, property, MONTH(table1.created) as monthFROM table1left JOIN table2ON table2.barcode=table1.inputgroup by department, property, month";$res=mysqli_query($con,$sql);                                                   while($row=mysqli_fetch_array($res)){$time = $row['month'];    if (!array_key_exists($time, $csvArray) ) {        foreach ($departments as $department) {            $csvArray[$department] = array();            foreach($properties as $property) {                $csvArray[$department][$property] = array();            }        }    }    $department = $row['department'];    $property = $row['property'];    $total_no_students = $row['total_no_students'];    $csvArray[$time][$departments][$property] = $total_no_students;}

任何帮助如何使用 php 将查询转换为上表？

Answer 1

select "month",
       SUM(case when department = 'Physics' and property = 'undergraduate' then total_no_students else 0 end) as "Physics-undergrad",
       [...]
from table_name
group by "month"