Oracle 18c

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

CREATE TABLE table_name (
  col1 NUMBER,
  col2 NUMBER,
  col3 NUMBER,
  col4 NUMBER,
  col5 NUMBER,
  col6 NUMBER,
  col7 NUMBER,
  col8 NUMBER,
  col9 NUMBER,
  col10 NUMBER
)
INSERT INTO table_name
SELECT NULL,    1,    1, NULL, NULL, NULL, NULL, NULL,    2, NULL FROM DUAL UNION ALL
SELECT    1, NULL, NULL,    2, NULL,    1, NULL, NULL,    2, NULL FROM DUAL UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM DUAL UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,    2, NULL FROM DUAL;
4 rows affected

SELECT LISTAGG( value, ' ' ) WITHIN GROUP ( ORDER BY value ) AS vals
FROM   (
  SELECT DISTINCT
         rn,
         value,
         COUNT(value) OVER ( PARTITION BY rn ) AS num_values
  FROM   (
    SELECT ROWNUM AS rn,
           t.*
    FROM   table_name t
  )
  UNPIVOT INCLUDE NULLS (
    value FOR key IN ( col1, col2, col3, col4, col5, col6, col7, col8, col9, col10 )
  )
)
WHERE num_values = 0
OR    value IS NOT NULL
GROUP BY rn


VALS


1 2

1 2

null

2

VALS
1 2
1 2
null
2