<?php
$query = "SELECT VERSION() as version;";
$colsQuery =
"
SELECT CONCAT('[', FORMAT(month_year, 'MMM yyyy'), ']')
FROM (
SELECT DISTINCT EOMONTH(tis_posting_date) AS month_year
FROM tis_temp_dsr
WHERE tis_posting_date BETWEEN '01-01-2025' AND '03-31-2025'
) t
ORDER BY month_year
";
$cols = implode(',', $pdo->query($colsQuery)->fetchAll(PDO::FETCH_COLUMN)).',[Total]';
echo $cols."\n";
$query =
"
with cte as
(
select tis_customer_no,tis_account_name,
month_year = FORMAT(tis_posting_date, 'MMM yyyy'), tis_amount_with_vat
from tis_temp_dsr
union all
select tis_customer_no,tis_account_name,
month_year = 'Total', tis_amount_with_vat = sum(tis_amount_with_vat)
from tis_temp_dsr
group by tis_customer_no,tis_account_name
)
SELECT tis_customer_no,tis_account_name,
".$cols."
from
(
select tis_customer_no,tis_account_name, month_year, tis_amount_with_vat
from cte
-- the following is for row wise total
union all
select tis_customer_no = '',tis_account_name = 'Total', month_year, sum(tis_amount_with_vat)
from cte
group by month_year
) x
pivot
(
Sum(tis_amount_with_vat)
for month_year in (".$cols.")
) p
";
echo $query;
print_r($db->query($query)->fetchAll(PDO::FETCH_ASSOC));