CREATE PROCEDURE "sp_CrystalMultilanguageTable"
AS
CODE NVARCHAR(30);
TRANSL NCLOB;
LANG INTEGER;
STR NCLOB;
CODE_OLD NVARCHAR(30);
NAME NVARCHAR(30);

CURSOR curTemp FOR SELECT CR."Code",  IFNULL(T1."Trans", CR."Name") AS "TRANSLATION", T1."LangCode" ,CR."Name"
    FROM "@LAYOUTTRANSLATIONS" CR
        LEFT OUTER JOIN OMLT T0 ON T0.PK = CR."Code" AND T0."TableName" = '@LAYOUTTRANSLATIONS' AND T0."FieldAlias" = 'Name'
        LEFT JOIN MLT1 T1 ON T0."TranEntry" = T1."TranEntry"
        ORDER BY CR."Code", T1."LangCode" ;

BEGIN

create local temporary row table #t1 (CODE1 NVARCHAR(30), TRANSLATION1 NCLOB, "LangCode" Integer);
STR := 'SELECT ';

OPEN curTemp ;
FETCH curTemp INTO CODE, TRANSL, LANG, NAME;
CODE_OLD := '';
WHILE NOT curTemp::NOTFOUND DO
if :CODE_OLD <> :CODE THEN
	STR := :STR || ' MAX(CASE WHEN "CODE1" = ''' || :CODE || ''' THEN SUBSTRING("TRANSLATION1",1,8000) ELSE '''' END) AS "' || :NAME || '", ';
	CODE_OLD := :CODE;
END IF;
	insert into #t1 values (:CODE, :TRANSL, :LANG);
FETCH curTemp INTO CODE, TRANSL, LANG, NAME;
END WHILE;
CLOSE curTemp;
STR := :STR || ' "LangCode" ';
STR := :STR || ' FROM #t1 GROUP BY "LangCode"';
EXECUTE IMMEDIATE :STR;
DROP TABLE #t1;

--SELECT :STR FROM DUMMY;
END;