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 table_name(dx89, dx90, dx91, dx92) AS
SELECT 'A', 'B', NULL, 'D' FROM DUAL UNION ALL
SELECT NULL, NULL, 'C', NULL FROM DUAL UNION ALL
SELECT 'A', 'B', 'C', 'D' FROM DUAL UNION ALL
SELECT ';;', NULL, NULL, NULL FROM DUAL
4 rows affected
SELECT SUBSTR(
CASE WHEN dx89 IS NOT NULL THEN ';'||dx89 END
|| CASE WHEN dx90 IS NOT NULL THEN ';'||dx90 END
|| CASE WHEN dx91 IS NOT NULL THEN ';'||dx91 END
|| CASE WHEN dx92 IS NOT NULL THEN ';'||dx92 END,
2
) AS diagnoses2
FROM table_name;
| DIAGNOSES2 |
|---|
| A;B;D |
| C |
| A;B;C;D |
| ;; |
SELECT SUBSTR(
NVL2(dx89, ';'||dx89, NULL)
|| NVL2(dx90, ';'||dx90, NULL)
|| NVL2(dx91, ';'||dx91, NULL)
|| NVL2(dx92, ';'||dx92, NULL),
2
) AS diagnoses2
FROM table_name;
| DIAGNOSES2 |
|---|
| A;B;D |
| C |
| A;B;C;D |
| ;; |