Hi Bhavbhuti!

Let's split this into two parts.

Running

EXECUTE BLOCK returns (Mystatement varchar(4096)) AS
DECLARE VARIABLE S VARCHAR(256);
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE S2 VARCHAR(256);
DECLARE VARIABLE SIIF VARCHAR(1024);
DECLARE VARIABLE SJOIN VARCHAR(1024);
BEGIN
S='';
S2='';
SIIF='';
SJOIN='';
I=1;
FOR SELECT 'LEFT JOIN MACCOUNTS MA'||:I||' ON MA'||:I||'.CCODE = '''||CCODE||
''' AND MA'||:I||'.IID = r.IACCOUNTID ', 'SUM(IIF(MA'||:I||'.IID IS NOT NULL, r.BAMT, 0))'
FROM MACCOUNTS MA1
WHERE MA1.CCODE > ''
AND NOT EXISTS(SELECT * FROM MACCOUNTS MA2
WHERE MA1.CCODE = MA2.CCODE
AND MA1.IID > MA2.IID) INTO :S, S2 DO
BEGIN
SJOIN = SJOIN||S;
SIIF = SIIF||', '||S2;
I=I+1;
END

FOR WITH TMP(FIELD_NAMES) AS
(SELECT LIST(CCODE) FROM MACCOUNTS MA1
WHERE MA1.CCODE > ''
AND NOT EXISTS(SELECT * FROM MACCOUNTS MA2
WHERE MA1.CCODE = MA2.CCODE
AND MA1.IID > MA2.IID))
SELECT 'WITH TMP(IPID,'||FIELD_NAMES||') AS (SELECT r.IPID'||:SIIF||
'FROM SSALEINVOICEFOOTER r ' || :SJOIN||' GROUP BY 1) SELECT * FROM TMP'
FROM TMP INTO :MyStatement DO
SUSPEND;
END;

will return a statement that, when run, will return almost the result you want ('almost' being that it returns 0 and not NULL for columns not existing and that you may have to do some modifications if there may exist named rows in MACCOUNTS that doesn't exist in SSALEINVOICEFOOTER and hence, shouldn't be a column).

I still think this is far more complicated than doing such conversions using PivotTables in Excel or similar.

HTH,
Set