Pivot 2 таблицы с базой столбцов года и месяца в диапазоне дат ⇐ Php

[Anonymous]

Я хочу повернуть две таблицы, используя SQL Server и Php. < /p>
Таблица 1: < /p>

Код: Выделить всё

accountname amount dateposted
ab01         100    jan 1, 2022
ab02         100    jan 1, 2022
ab03         100    jan 1, 2023
< /code>
Таблица 2: < /p>
accountname target
ab01         100
ab02         100
ab03         100
< /code>
table или таблица Pivot: < /p>
accountname  jan2022 jan2023   total target percentage
ab01         100               100    100    100%
ab02         100               100    100    100%
ab03                  100      100    100    100%
< /code>
Это пробое до сих пор: < /p>
$stmt = $database->prepare("SELECT DISTINCT FORMAT(tis_posting_date, 'MMM yyyy') AS month_year
FROM tis_temp_dsr
WHERE tis_posting_date BETWEEN '2022-11-29' AND '2023-03-29'");
$stmt->execute();
$columns_result = $stmt->fetchAll();
$columns = [];
foreach ($columns_result as $row) {
$columns[] = "[" . $row['month_year'] . "]";
}

$column_list = implode(", ", $columns);

$start_date = '2024-12-01';
$end_date = '2025-03-31';

$sql_columns = "
SELECT DISTINCT FORMAT(tis_posting_date, 'MMM yyyy') AS month_year
FROM tis_temp_dsr
WHERE tis_posting_date BETWEEN '$start_date' AND '$end_date'
";

$columns_result = $database->query($sql_columns);

$columns = [];
foreach ($columns_result as $row) {
$columns[] = "[" . $row['month_year'] . "]";
}

$column_list = implode(", ", $columns);

$sql = "
SELECT tis_account_name, $column_list
FROM (
SELECT
tis_account_name,
FORMAT(tis_posting_date, 'MMM yyyy') AS month_year,
tis_amount_with_vat
FROM tis_temp_dsr
WHERE tis_posting_date BETWEEN '$start_date' AND '$end_date'
) AS SourceTable
PIVOT (
SUM(tis_amount_with_vat)
FOR month_year IN ($column_list)
) AS PivotTable
";

$result = $database->query($sql);

Я просто прокомментирую общую сумму, потому что на ней есть ошибка.
Заранее спасибо.>

Подробнее здесь: https://stackoverflow.com/questions/795 ... date-range