I agree: more complicated, a lot. And still less dynamic and powerfull.

------------------------------
Em sáb, 28 de dez de 2013 18:02 MSK Svein Erling Tysvær escreveu:

>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
>
>------------------------------------
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://www.firebirdsql.org and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>Yahoo Groups Links
>
>
>