-- This is one of the best examples to use CASE. In this case we have three -- strings that need to be concatenated with a comma. This will be complex -- using DECODE function since three columns have to be compared. WITH q_tab AS (SELECT 1 idx, '1aa' c1, NULL c2, '1cc' c3 FROM DUAL UNION SELECT 2 idx, NULL c1, '2bb' c2, '2cc' c3 FROM DUAL UNION SELECT 3 idx, '3aa' c1, '3bb' c2, NULL c3 FROM DUAL UNION SELECT 4 idx, '4aa' c1, '4bb' c2, '4cc' c3 FROM DUAL UNION SELECT 5 idx, NULL c1, NULL c2, NULL c3 FROM DUAL) SELECT c1, c2, c3, CASE WHEN c1 IS NULL AND c2 IS NULL AND c3 IS NULL THEN NULL WHEN c1 IS NOT NULL AND c2 IS NOT NULL AND c3 IS NULL THEN c1||','||c2 WHEN c1 IS NULL AND c2 IS NOT NULL AND c3 IS NOT NULL THEN c2||','||c3 WHEN c1 IS NOT NULL AND c2 IS NULL AND c3 IS NOT NULL THEN c1||','||c3 ELSE C1||','||c2||','||C3 END concat_string FROM q_tab;
56409