SQLize Online / PHPize Online  /  SQLtest Online

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>customer_no</th><th>account_name</th><th>','Jan2025</th><th>Feb2025</th><th>Mar2025</th><th>Apr2025','</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(Jan2025,0),'</td>')+concat('<td>',coalesce(Feb2025,0),'</td>')+concat('<td>',coalesce(Mar2025,0),'</td>')+concat('<td>',coalesce(Apr2025,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 ([Jan2025],[Feb2025],[Mar2025],[Apr2025]) )pvt