Pivot 2 tables with year and month column base on date range

I want to pivot two tables using SQL Server and PHP.

table 1:

accountname amount dateposted
ab01         100    jan 1, 2022
ab02         100    jan 1, 2022
ab03         100    jan 1, 2023

table 2:

accountname target
ab01         100   
ab02         100    
ab03         100    

output table or pivot table:

accountname  jan2022 jan2023   total target percentage
ab01         100               100    100    100%
ab02         100               100    100    100% 
ab03                  100      100    100    100% 

This is tried so far:

$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);

I just comment the Total, because there's an error on it.

Thanks in advance.

It worked when using one table, and there's no date range.

It looks like a dynamic PIVOT, so a possible approach here is a dynamic statement. A simplified example, without the WHERE clause and based on your test data, is the following statement:

-- Column list
DECLARE @col nvarchar(MAX)
SELECT @col = STUFF(
    (
    SELECT CONCAT(N',[', FORMAT(month_year, 'MMM yyyy'), N']')
    FROM (
        SELECT DISTINCT EOMONTH(dateposted) AS month_year
        FROM table1
    ) t
    ORDER BY month_year
    FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'), 1, 1, ''
)

-- Dynamic PIVOT
DECLARE @stm nvarchar(MAX)
DECLARE @err int
SET @stm = 
    N'SELECT accountname, ' + @col + N', target ' +
    N'FROM ('+
        N'SELECT t1.accountname, t1.amount, t2.target, FORMAT(dateposted, ''MMM yyyy'') AS month_year ' +
        N'FROM table1 t1 ' +
        N'LEFT JOIN table2 t2 ON t1.accountname = t2.accountname ' +
    N') t ' +
    N'PIVOT (SUM(amount) FOR month_year IN (' + @col + ')) p '
EXEC @err = sp_executesql @stm
IF @err <> 0 SELECT 'Error' AS Result

The next step is to execute this statement with PHP. The following example demonstrates the solution with PHP Driver for SQL Server. As an additional note, always try to use parameterized statements to prevent possible SQL injection issues.

<?php
$server = 'server\instance,port';
$database = 'database';
$uid = 'uid';
$pwd = 'pwd';

try {
    $conn = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
    die( "Error connecting to SQL Server".$e->getMessage());
}

try {

    $tsql = "
        SET NOCOUNT ON
        -- Dates
        DECLARE @date1 datetime
        DECLARE @date2 datetime
        SET @date1 = ?
        SET @date2 = ?
        -- Column list
        DECLARE @col nvarchar(MAX)
        SELECT @col = STUFF(
            (
            SELECT CONCAT(N',[', FORMAT(month_year, 'MMM yyyy'), N']')
            FROM (
                SELECT DISTINCT EOMONTH(dateposted) AS month_year
                FROM table1
                WHERE dateposted BETWEEN @date1 AND @date2
            ) t
            ORDER BY month_year
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(max)'), 1, 1, ''
        )
        -- Dynamic PIVOT
        DECLARE @stm nvarchar(MAX)
        DECLARE @prm nvarchar(MAX)
        DECLARE @err int
        SET @stm = 
            N'SELECT accountname, ' + @col + N', target ' +
            N'FROM ('+
                N'SELECT t1.accountname, t1.amount, t2.target, FORMAT(dateposted, ''MMM yyyy'') AS month_year ' +
                N'FROM table1 t1 ' +
                N'LEFT JOIN table2 t2 ON t1.accountname = t2.accountname ' +
                N'WHERE dateposted BETWEEN @date1 AND @date2 ' +
            N') t ' +
            N'PIVOT (SUM(amount) FOR month_year IN (' + @col + ')) p '
        SET @prm = N'@date1 datetime, @date2 datetime'
        EXEC @err = sp_executesql @stm, @prm, @date1, @date2
        IF @err <> 0 SELECT 'Error' AS Result
    ";

    $date1 = '20221201';
    $date2 = '20230331';
    $stmt = $conn->prepare($tsql);
    $stmt->bindParam(1, $date1, PDO::PARAM_STR);
    $stmt->bindParam(2, $date2, PDO::PARAM_STR);
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        foreach ($row as $name => $value) {
            echo $name. ": " . $value . "<br>";
        }
        echo "<br>";
    }
    
} catch (PDOException $e) {
    die ("Error executing query. ".$e->getMessage());
}

$stmt = null;
$conn = null;
?>