SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE results (
  result_id INTEGER PRIMARY KEY, 
  competition_id INTEGER,
  competitor_id INTEGER,
  competitor_ranking INTEGER
);
✓
INSERT INTO results(competition_id, competitor_id, competitor_ranking) VALUES 
  (1, 1, 0.1), (1, 2, 0.4), (1, 3, 0.2), (1, 4, 0.3),
  (2, 1, 0.4), (2, 2, 0.1), (2, 3, 0.2), (2, 5, 0.3),
  (3, 3, 0.1), (3, 4, 0.4), (3, 5, 0.2), (3, 6, 0.3);
✓
WITH competition AS (
    SELECT DISTINCT
      r.competition_id
    FROM results r
),
competitor AS (
    SELECT DISTINCT
      r.competitor_id
    FROM results r
)
SELECT
  cr.competitor_id,
  GROUP_CONCAT(IFNULL(r.competition_id, 'M'), '; ') AS competitions,
  GROUP_CONCAT(r.competitor_ranking, '; ') AS rankings,
  GROUP_CONCAT(IFNULL(r.competitor_ranking, '+1.0'), '; ') AS ranking_with_penalties
FROM competitor cr
CROSS JOIN competition cn
LEFT JOIN results r
   ON r.competition_id = cn.competition_id
  AND r.competitor_id = cr.competitor_id
GROUP BY
  cr.competitor_id;


competitor_id
competitions
rankings
ranking_with_penalties


1
1; 2; M
0.1; 0.4
0.1; 0.4; +1.0

2
1; 2; M
0.4; 0.1
0.4; 0.1; +1.0

3
1; 2; 3
0.2; 0.2; 0.1
0.2; 0.2; 0.1

4
1; M; 3
0.3; 0.4
0.3; +1.0; 0.4

5
M; 2; 3
0.3; 0.2
+1.0; 0.3; 0.2

6
M; M; 3
0.3
+1.0; +1.0; 0.3

competitor_id	competitions	rankings	ranking_with_penalties
1	1; 2; M	0.1; 0.4	0.1; 0.4; +1.0
2	1; 2; M	0.4; 0.1	0.4; 0.1; +1.0
3	1; 2; 3	0.2; 0.2; 0.1	0.2; 0.2; 0.1
4	1; M; 3	0.3; 0.4	0.3; +1.0; 0.4
5	M; 2; 3	0.3; 0.2	+1.0; 0.3; 0.2
6	M; M; 3	0.3	+1.0; +1.0; 0.3