Dynamically Transpose Row into Column

Question

I am trying to have the query result show the sku on the first column and the rest of the distinct months following and the quantities of each under the certain month.

SELECT DATE_FORMAT(date_defect,'%Y-%m') AS date, sku AS 'sku', COUNT(sku) AS 'COUNT' 
FROM defect 
WHERE date_defect 
BETWEEN '".$report->sDate."' AND '".$report->fDate."' 
GROUP BY date,sku 

I want to be able to choose the date range which is why I have those objects as the conditions for dates. Currently this is how the table looks

DATE	SKU	COUNT
MONTH1	SKU-1	COUNT1
MONTH1	SKU-2	COUNT2
MONTH2	SKU-1	COUNT1
MONTH2	SKU-2	COUNT2

This is how I would like the table to look

SKU	MONTH1	MONTH2	MONTH3
SKU-1	COUNT1	COUNT2	COUNT3
SKU-2	COUNT1	COUNT2	COUNT3

Answer 1

Rather than post a screenshot of your data, include it in your question. You're making it hard for others to help you.

Given this reduced sample data:

$data = array(
  array('date' => '2022-01', 'sku' => 'C1S', 'COUNT' => 1),
  array('date' => '2022-01', 'sku' => 'F8M', 'COUNT' => 3),

  array('date' => '2022-02', 'sku' => 'F8M', 'COUNT' => 4),
  array('date' => '2022-02', 'sku' => 'K1M', 'COUNT' => 6),


  array('date' => '2022-03', 'sku' => 'F8M', 'COUNT' => 3),
  array('date' => '2022-03', 'sku' => 'K1M', 'COUNT' => 10),
);

You want to:

1. Iterate over every entry in the array.
2. Every item in the array should be put into its own group, based on the SKU.
3. Inside of those groups, you want to easily find the data by date.
4. To make the columns, you need to know every date you're going to show.

You could make a static list of months for step 4. This example generates the list of used dates from the input.

$grouped = array(
  // Will look like this:
  // array(
  //   "F8M" => array(
  //     "2022-01" => ...
  //   ), 
  //   ...
  // );
);
$all_dates = array();
foreach($data as $current) {
  // Assume $current == array('data' => '2022-01', 'sku' => 'F8M', ...)

  // If $grouped['F8M'] doesn't exist yet, set it to an empty array.
  if(!isset($grouped[$current['sku']])) {
      $grouped[$current['sku']] = array(); 
  }

  // You could also set it to $current['COUNT'],
  // but this way, if you add more columns later, you have the entire 
  // database row.
  $grouped[$current['sku']][$current['date']] = $current; 

  // You need the dates to make the columns later.
  $all_dates[] = $current['date'];
}

// Remove duplicates.
$all_dates = array_unique($all_dates);
// sort($all_dates); should not be needed here in this case.

Then just iterate to make your table:

echo "
<table>
  <thead>
    <tr>
      <th>SKU</th>
";
// Create a column for every date.
foreach($all_dates as $date) {
  echo "      <th>$date</th>\n";
}
echo "
    </tr>
  </thead>
  <tbody>
";

// Then show every item.
foreach($grouped as $sku => $sku_dates) {
  echo "
    <tr>
      <th>$sku</th>
";

  // Not all SKUs have data for every month, so we iterate
  // over the header columns again.
  foreach($all_dates as $date) {
    if (empty($sku_dates[$date])) {
      // Example: $data['C1S']['2022-02'] is empty(),
      // so this column is blank.
      echo "      <td> - </td>\n";
    } else {
      // Example: $data['F8M']['2022-02']['COUNT'] exists
      // and this column contains 4.
      echo "      <td>{$sku_dates[$date]['COUNT']}</td>\n";
    }
  }
  echo "
    </tr>
";
}

echo "
  </tbody>
</table>
";

If you break down the problem into the steps I mentioned, the solution should be easy to understand and you might not even need the example listed above.