Pivot 2 -Tabellen mit der Spalte Basis der Jahres- und Monats -Basis zum Datumsbereich ⇐ Php

[Anonymous]

Ich möchte zwei Tabellen mit SQL Server und php. < /p>
Tabelle 1: < /p>
drehen

Code: Select all

accountname amount dateposted
ab01         100    jan 1, 2022
ab02         100    jan 1, 2022
ab03         100    jan 1, 2023
< /code>
Tabelle 2: < /p>
accountname target
ab01         100
ab02         100
ab03         100
< /code>
Ausgabetabelle oder Pivot -Tabelle: < /p>
accountname  jan2022 jan2023   total target percentage
ab01         100               100    100    100%
ab02         100               100    100    100%
ab03                  100      100    100    100%
< /code>
Dies wird bisher ausprobiert: < /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);

Ich kommentiere nur die Gesamtsumme, da ein Fehler darauf vorliegt.
Danke im Voraus.>