-- create procedure
DECLARE
   CURSOR problems_cursor IS
   SELECT *
    FROM problems;
    pr_id NUMBER;
    pr_name VARCHAR2(40);
    l_sql VARCHAR2(2048);
    l_from_part VARCHAR2(2048);
BEGIN
    l_sql := 'CREATE OR REPLACE VIEW acm_results AS SELECT teams.team_name';
    l_from_part := 'from teams';
    OPEN problems_cursor;
    LOOP
    FETCH problems_cursor INTO pr_id, pr_name;
        EXIT WHEN problems_cursor%notfound;
        l_sql := l_sql || ', CASE WHEN max(att' || pr_id || '.is_correct) = 1 THEN ''+'' ELSE ''-'' END AS ' || pr_name;
        l_from_part := l_from_part || ' LEFT JOIN attempts att' || pr_id || ' ON teams.team_id  = att' || pr_id || '.team_id AND att' || pr_id || '.problem_id = ' || pr_id;
    END LOOP;
    CLOSE problems_cursor;
    l_sql := l_sql || ' ' || l_from_part || ' GROUP BY(teams.team_name)';
    DBMS_OUTPUT.put_line(l_sql);
    EXECUTE IMMEDIATE l_sql;
END;

--- to query view
SELECT * FROM acm_results;