gistfile1.txt

set @sql = null;
SELECT
	GROUP_CONCAT(DISTINCT
		CONCAT(
			'SUM(IF(name = ''',name,''',cb.amount,0)) AS ', replace(name, ' ', '_')
		)
	) INTO @sql
from property_headancillaries;

set @sql = CONCAT(
		'SELECT
		pu.unit_name, t3.rental, t1.surcharge, t1.advance_rental, t1.security_deposit, t2.*
		from property_units as pu
		LEFT JOIN (
				SELECT 
					t3b.unit_id,
					(
						select sum(cx.total) from contract_details as cx where cx.unit_id = t3b.unit_id and t3b.id = cx.id
					) as rental
				FROM contract_headers as t3a
				INNER JOIN contract_details as t3b on t3a.id = t3b.header_id
				LEFT JOIN property_charges_headers as t3c on t3c.contract_id = t3a.id
				WHERE t3a.status in("Approved","Renewed","Terminate") and YEAR(t3c.date) = 2019
							and MONTH(t3c.date) = 8 and t3c.status = "Approved"
				GROUP BY t3b.unit_id
		) as t3 on pu.id = t3.unit_id
		LEFT JOIN (
					SELECT
							d.asset_id as asset_id,
							d.unit_id as unit_id,
							SUM(IF(charges_type = 1,b.amount,0)) as rental,
							SUM(IF(charges_type = 3,b.amount,0)) as advance_rental,
							SUM(IF(charges_type = 4,b.amount,0)) as security_deposit,
							SUM(IF(charges_type = 5,b.amount,0)) as surcharge,
							SUM(IF(charges_type = 6,b.amount,0)) as others,
							a.date as date ,
							a.due_date as due_date
					FROM property_charges_headers as a
					INNER JOIN property_charges_details as b on a.id = b.header_id
					LEFT JOIN contract_headers as c on c.id = a.contract_id
					LEFT JOIN contract_details as d on c.id = d.header_id
					WHERE charges_type in (1,3,4,5,6)
											and c.status in("Approved","Renewed","Terminate") and a.status = "Approved" and YEAR(a.date) = 2019 and MONTH(a.date) = 8
					GROUP BY d.unit_id
		) as t1 on pu.id = t1.unit_id
		LEFT  JOIN
		(	
				SELECT
						cf.unit_id as unit_id, ' , @sql ,'
				FROM property_charges_headers as ca
				INNER JOIN property_charges_details as cb on ca.id = cb.header_id
				LEFT JOIN contract_ancillaries as cc on cc.id = cb.charge_type_ref_id
				LEFT JOIN property_headancillaries as cd on cd.id = cc.ancillary_id
				LEFT JOIN contract_headers as ce on ce.id = ca.contract_id
				LEFT JOIN contract_details as cf on ce.id = cf.header_id
				WHERE ca.status in("Approved","Printed")
										and ce.status in("Approved","Renewed","Terminate")
										and cc.id is not null
										and cb.charges_type = 2 and YEAR(ca.date) = 2019 and MONTH(ca.date) = 8
				GROUP BY cf.unit_id
		) as t2 on t2.unit_id = pu.id
		where pu.asset_id = 2
		ORDER BY unit_name asc
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;