Oracle 21c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table1 ( idvar ) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL;
3 rows affected

CREATE TABLE table2 ( table1_idvar, criteria_col, col_of_interest1, col_of_interest2 ) AS
SELECT 1, 'foo', DATE '2021-01-01',  100 FROM DUAL UNION ALL
SELECT 1, 'foo', DATE '2021-03-01',  500 FROM DUAL UNION ALL
SELECT 1, 'foo', DATE '2021-06-01', 2000 FROM DUAL UNION ALL
SELECT 1, 'bar', DATE '2021-06-01', 2000 FROM DUAL UNION ALL
SELECT 2, 'foo', DATE '2021-01-02',  200 FROM DUAL UNION ALL
SELECT 2, 'foo', DATE '2021-03-02',  300 FROM DUAL UNION ALL
SELECT 2, 'bar', DATE '2021-06-02',  400 FROM DUAL UNION ALL
SELECT 3, 'foo', DATE '2021-01-03',  700 FROM DUAL;
8 rows affected

SELECT t1.IDvar,
       MIN(CASE WHEN t2.criteria_col = 'foo' THEN t2.col_of_interest1 END)
         AS foo_1_col_of_interest1,
       MIN(col_of_interest2) KEEP (
         DENSE_RANK FIRST ORDER BY
         CASE WHEN t2.criteria_col = 'foo' THEN t2.col_of_interest1 END
         ASC NULLS LAST
       ) AS foo_1_col_of_interest2,
       MAX(CASE WHEN t2.criteria_col = 'foo' THEN t2.col_of_interest1 END)
         AS foo_2_col_of_interest1,
       MAX(col_of_interest2) KEEP (
         DENSE_RANK FIRST ORDER BY
         CASE WHEN t2.criteria_col = 'foo' THEN t2.col_of_interest1 END
         DESC NULLS LAST
       ) AS foo_2_col_of_interest2
FROM   table1 t1
       FULL JOIN table2 t2
       ON t1.IDvar = t2.table1_IDvar
GROUP BY t1.IDvar


IDVAR
FOO_1_COL_OF_INTEREST1
FOO_1_COL_OF_INTEREST2
FOO_2_COL_OF_INTEREST1
FOO_2_COL_OF_INTEREST2


1
2021-01-01 00:00:00
100
2021-06-01 00:00:00
2000

2
2021-01-02 00:00:00
200
2021-03-02 00:00:00
300

3
2021-01-03 00:00:00
700
2021-01-03 00:00:00
700


SELECT idvar,
       MAX(CASE WHEN criteria_col = 'foo' AND rn = 1 THEN col_of_interest1 END)
         AS foo_1_col_of_interest1,
       MAX(CASE WHEN criteria_col = 'foo' AND rn = 1 THEN col_of_interest2 END)
         AS foo_1_col_of_interest2,
       MAX(CASE WHEN criteria_col = 'foo' AND rn = 2 THEN col_of_interest1 END)
         AS foo_2_col_of_interest1,
       MAX(CASE WHEN criteria_col = 'foo' AND rn = 2 THEN col_of_interest2 END)
         AS foo_2_col_of_interest2,
       MAX(CASE WHEN criteria_col = 'foo' AND rn = 3 THEN col_of_interest1 END)
         AS foo_3_col_of_interest1,
       MAX(CASE WHEN criteria_col = 'foo' AND rn = 3 THEN col_of_interest2 END)
         AS foo_3_col_of_interest2
FROM   (
  SELECT t1.IDvar,
         criteria_col,
         col_of_interest1,
         col_of_interest2,
         ROW_NUMBER() OVER (
           PARTITION BY t1.IDvar, criteria_col
           ORDER BY col_of_interest1, col_of_interest2
         ) AS rn
  FROM   table1 t1
         FULL JOIN table2 t2
         ON t1.IDvar = t2.table1_IDvar
  WHERE  criteria_col IN ('foo' /*, 'bar', 'etc'*/)
)
GROUP BY idvar


IDVAR
FOO_1_COL_OF_INTEREST1
FOO_1_COL_OF_INTEREST2
FOO_2_COL_OF_INTEREST1
FOO_2_COL_OF_INTEREST2
FOO_3_COL_OF_INTEREST1
FOO_3_COL_OF_INTEREST2


1
2021-01-01 00:00:00
100
2021-03-01 00:00:00
500
2021-06-01 00:00:00
2000

2
2021-01-02 00:00:00
200
2021-03-02 00:00:00
300
null
null

3
2021-01-03 00:00:00
700
null
null
null
null

IDVAR	FOO_1_COL_OF_INTEREST1	FOO_1_COL_OF_INTEREST2	FOO_2_COL_OF_INTEREST1	FOO_2_COL_OF_INTEREST2
1	2021-01-01 00:00:00	100	2021-06-01 00:00:00	2000
2	2021-01-02 00:00:00	200	2021-03-02 00:00:00	300
3	2021-01-03 00:00:00	700	2021-01-03 00:00:00	700

IDVAR	FOO_1_COL_OF_INTEREST1	FOO_1_COL_OF_INTEREST2	FOO_2_COL_OF_INTEREST1	FOO_2_COL_OF_INTEREST2	FOO_3_COL_OF_INTEREST1	FOO_3_COL_OF_INTEREST2
1	2021-01-01 00:00:00	100	2021-03-01 00:00:00	500	2021-06-01 00:00:00	2000
2	2021-01-02 00:00:00	200	2021-03-02 00:00:00	300	null	null
3	2021-01-03 00:00:00	700	null	null	null	null