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
