使用Year Month Day Columns重新格式化表，以便每个日期都是PHP中的列标题

Question

Suppose you have an associative array in PHP with tabular data that looks like the following:

KPIName     Year    Month   Day KPICount
AEAD10D-LRZ 2014    7       30  29
AEAD10D-LRZ 2014    7       31  40
AEAD10D-LRZ 2014    8       1   49
AEAD10D-LRZ 2014    8       2   34
AEAD10D-LRZ 2014    8       3   32
AEAD10D-LRZ 2014    8       4   30
AEAD10D-LRZ 2014    8       5   30
AEAD20D-LRZ 2014    7       30  25
AEAD20D-LRZ 2014    7       31  31
AEAD20D-LRZ 2014    8       1   47
AEAD20D-LRZ 2014    8       2   17
AEAD20D-LRZ 2014    8       3   12
AEAD20D-LRZ 2014    8       4   37
AEAD20D-LRZ 2014    8       5   30

What is the most performant way to reformat this into the following data structure:

KPIName     2014-07-30  2014-07-31  2014-08-01  2014-08-02  2014-08-03  2014-08-04  2014-08-05
AEAD10D-LRZ 29          40          49          34          32          30          30
AEAD20D-LRZ 25          31          47          17          12          37          30

Answer 1

There is multiple ways to do this, here is one way.

// your associative array
$data = array(
array("KPIName"=>"AEAD10D-LRZ", "Year"=>2014, "Month"=>7, "Day"=>30, "KPICount"=>29),
array("KPIName"=>"AEAD10D-LRZ", "Year"=>2014, "Month"=>7, "Day"=>31, "KPICount"=>40),
array("KPIName"=>"AEAD10D-LRZ", "Year"=>2014, "Month"=>8, "Day"=>1, "KPICount"=>49),
array("KPIName"=>"AEAD10D-LRZ", "Year"=>2014, "Month"=>8, "Day"=>2, "KPICount"=>34),
array("KPIName"=>"AEAD10D-LRZ", "Year"=>2014, "Month"=>8, "Day"=>3, "KPICount"=>32),
array("KPIName"=>"AEAD10D-LRZ", "Year"=>2014, "Month"=>8, "Day"=>4, "KPICount"=>30),
array("KPIName"=>"AEAD10D-LRZ", "Year"=>2014, "Month"=>8, "Day"=>5, "KPICount"=>30),
array("KPIName"=>"AEAD20D-LRZ", "Year"=>2014, "Month"=>7, "Day"=>30, "KPICount"=>25),
array("KPIName"=>"AEAD20D-LRZ", "Year"=>2014, "Month"=>7, "Day"=>31, "KPICount"=>31),
array("KPIName"=>"AEAD20D-LRZ", "Year"=>2014, "Month"=>8, "Day"=>1, "KPICount"=>47),
array("KPIName"=>"AEAD20D-LRZ", "Year"=>2014, "Month"=>8, "Day"=>2, "KPICount"=>17),
array("KPIName"=>"AEAD20D-LRZ", "Year"=>2014, "Month"=>8, "Day"=>3, "KPICount"=>12),
array("KPIName"=>"AEAD20D-LRZ", "Year"=>2014, "Month"=>8, "Day"=>4, "KPICount"=>37),
array("KPIName"=>"AEAD20D-LRZ", "Year"=>2014, "Month"=>8, "Day"=>5, "KPICount"=>30),
);

// array to hold reordered array
$newdata = array();

// reorder your array to $newdata, grouped by KPIName with the data as the key
foreach($data as $row){
    $newdata[$row["KPIName"]][$row["Year"]."-".$row["Month"]."-".$row["Day"]]=$row["KPICount"];

}

// get the date keys to use as headers
$headers = array_keys(reset($newdata));

// build the table
echo "<table><tr>";
echo "<td>KPIName</td>";
// loop through the date headers
foreach($headers as $header){
    echo "<td>{$header}</td>";
}
echo "</tr>";

// loop through each KPIName row
foreach($newdata as $key=>$row){
    echo "<tr>";
    echo "<td>{$key}</td>";

    // loop through each date value
    foreach($row as $data){
        echo "<td>{$data}</td>";
    }
    echo "</tr>";
}
echo "</table>";