PHPize Online / SQLize Online  /  SQLtest Online

<?php $pdo->query("create table tis_temp_dsr (customer_no varchar(9), customer_name varchar(9), tis_posting_date datetime, tis_customer_no int,tis_account_name varchar(9),tis_amount_with_vat int)"); $pdo->query("insert into tis_temp_dsr values ('bla', 'bla', '2025-01-01',1,'bla',10), ('hey', 'bart', '2025-02-02',2,'ney',20)"); $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."\n"; $results = $pdo->query($query)->fetchAll(PDO::FETCH_ASSOC); //print_r($results); foreach([ -1 => array_keys($results[0]) ] + $results as $line) echo implode(' | ', array_map(function($x) { return str_pad($x ?? 'null', 16); }, $line))."\n";