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';
-- set @startDate :='2025-01-01';
-- set @endDate :='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 *
from(
select coalesce(tis_customer_no,'AllCustomers')tis_customer_no
,coalesce(tis_account_name,'All accounts') 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 '2025-01-01' and '2025-03-31'
group by cube(tis_customer_no, tis_account_name, format(tis_posting_date,'MMMyyyy'))
having format(tis_posting_date,'MMMyyyy') is not null
and (
(tis_customer_no is not null and tis_account_name is not null)
or(tis_customer_no is null and tis_account_name is null)
)
)src
pivot
(
Sum(tSum)
for mon in ([Jan2025],[Feb2025],[Mar2025],[Apr2025])
)pvt
Telegram: DB developers channel