create table tis_temp_dsr(tis_customer_no varchar(10),tis_account_name varchar(30)
,tis_amount_with_vat float,tis_posting_date date);
insert into tis_temp_dsr (tis_customer_no,tis_account_name,tis_amount_with_vat,tis_posting_date) values
('20','Juan', 8,'2025-01-15')
,('20','Juan',12,'2025-01-16')
,('20','Juan',10,'2025-01-10')
,('20','Juan',10,'2025-03-11')
,('20','Juan',10,'2025-03-12')
,('20','Juan',30,'2025-03-22')
,('20','Juan',40,'2025-04-04')
,('20','Juan',50,'2025-05-05')
,('20','Juan',60,'2025-06-06')
,('21','Other',77,'2025-01-06')
;
select * from tis_temp_dsr;
declare @startDate date='2025-01-01';
declare @endDate date='2025-03-31';
select concat(
'table><thead>'
,'<tr><th>',Jan2025,Feb2025,Mar2025,',','</th><th>')
,'</th><th>total</th></tr></thead>') ths
,concat('<tbody>','<tr>'
,string_agg(
concat('<td>',tis_customer_no,'</td>')
+concat('<td>',tis_account_name,'</td>')
+concat('<td>',coalesce(Jan2025,0),'</td>')
+concat('<td>',coalesce(Feb2025,0),'</td>')
+concat('<td>',coalesce(Mar2025,0),'</td>')
+concat('<td>',coalesce(Total,0),'</td>')
,'')
,'</tr>','</tbody>')rs
from(
select tis_customer_no, tis_account_name
,format(tis_posting_date,'MMMyyyy') mon
,sum(tis_amount_with_vat) tSum
,sum(sum(tis_amount_with_vat))over(partition by tis_customer_no, tis_account_name )total
from tis_temp_dsr
where tis_posting_date between @startDate and @endDate
group by tis_customer_no, tis_account_name, format(tis_posting_date,'MMMyyyy')
)src
pivot
(
Sum(tSum)
for mon in ([Jan2025],[Feb2025],[Mar2025])
)pvt
<?php
$startDate='2025-01-01';
$endDate='2025-04-30';
$fromDate = DateTime::createFromFormat('Y-m-d', $startDate);
$toDate = DateTime::createFromFormat('Y-m-d', $endDate);
$columns='';
for($c=$fromDate;$c <= $toDate; $c->modify('1 month'))
{
$columns=$columns.','.$c->format('M').'2025';
};
$columns=substr($columns,1);
printf("Columns= %s". PHP_EOL,$columns);
$query = "
select concat(
'<table><thead>'
,'<tr><th>customer_no</th><th>account_name</th><th>','"
.str_replace(',','</th><th>',$columns).
"','</th><th>'
,'total</th></tr></thead>') thead
,concat('<tbody>'
,string_agg(
concat('<tr>','<td>',tis_customer_no,'</td>')
+concat('<td>',tis_account_name,'</td>')
+concat('<td>',coalesce("
.str_replace(','
,",0),'</td>')+concat('<td>',coalesce("
,$columns)
.",0),'</td>')
+concat('<td>',coalesce(Total,0),'</td>','</tr>')
,'')
,'</tbody>')tbody
from(
select tis_customer_no, tis_account_name
,format(tis_posting_date,'MMMyyyy') mon
,sum(tis_amount_with_vat) tSum
,sum(sum(tis_amount_with_vat))over(partition by tis_customer_no, tis_account_name )total
from tis_temp_dsr
where tis_posting_date between :startDate and :endDate
group by tis_customer_no, tis_account_name, format(tis_posting_date,'MMMyyyy')
)src
pivot
(
Sum(tSum)
for mon in ([" .str_replace(',','],[',$columns) ."])
)pvt
";
printf( PHP_EOL.$query. PHP_EOL);
$stmt = $pdo->prepare($query);
$stmt->execute([':startDate'=>$startDate,':endDate'=>$endDate]);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($row);
$result=$row['thead'].$row['tbody'];
printf(PHP_EOL.$result);
?>