How to collapse all nulls for every table column in a SQL statement
February 13th, 2007 By Frank Zhou
The following SQL query pattern can be used to collapse all nulls for every table column in a SQL statement.
CREATE table t5 as
SELECT 1 as ID, 'A' as A, to_char(null) as B, 'C' as C , to_char(null) as D
FROM dual
UNION ALL
SELECT 2, to_char(null), 'B', to_char(null) as C ,'D'
FROM dual
UNION ALL
SELECT 3, 'A1', to_char(null) as B, to_char(null) as C, 'D1'
FROM dual
UNION ALL
SELECT 4, to_char(null) as A ,'B1', 'C1', to_char(null) as D
FROM dual;
SELECT * FROM t5;
ID A B C D
---------- -- -- -- --
1 A C
2 B D
3 A1 D1
4 B1 C1
SELECT MAX(A) AS A,
MAX(B) AS B,
MAX(C) AS C,
MAX(D) AS D
FROM
(SELECT row_number( ) OVER (PARTITION BY LEV ORDER BY ID) AS rn,
lev,
A,
B,
C,
D
FROM t5,(SELECT LEVEL lev FROM dual CONNECT BY LEVEL <=4)
WHERE
CASE
WHEN Lev = 1
THEN A
WHEN Lev = 2
THEN B
WHEN Lev = 3
THEN C
WHEN Lev = 4
THEN D
END IS NOT NULL
)
GROUP BY RN;
A B C D
-- -- -- --
A B C D
A1 B1 C1 D1
July 1st, 2010 at 1:43 pm
If I change your data a little bit: CREATE table t5 as SELECT 1 as ID, 'A' as A, to_char(null) as B, 'C' as C , to_char(null) as D FROM dual UNION ALL SELECT 2, to_char(null), 'B', to_char(null) as C ,'D' FROM dual UNION ALL SELECT 3, 'A1', 'A' as B, to_char(null) as C, 'D1' FROM dual UNION ALL SELECT 4, to_char(null) as A ,'B1', 'C1', to_char(null) as D FROM dual; SELECT * FROM t5; ID A B C D ---------- -- -- -- -- 1 A C 2 B D 3 A1 A D1 -------- this row is different 4 B1 C1 your result will be: A B C D -- -- -- -- A B C D A1 B1 C1 D1 B1 C1 Here's my revised version: SELECT MAX(A) AS A, MAX(B) AS B, MAX(C) AS C, MAX(D) AS D FROM (SELECT row_number( ) OVER (PARTITION BY LEV ORDER BY ID) AS rn, DECODE(lev,1,A) A, DECODE(lev,2,B) B, DECODE(lev,3,C) C, DECODE(lev,4,D) D FROM t5,(SELECT LEVEL lev FROM dual CONNECT BY LEVEL <=4) WHERE CASE WHEN Lev = 1 THEN A WHEN Lev = 2 THEN B WHEN Lev = 3 THEN C WHEN Lev = 4 THEN D END IS NOT NULL ) GROUP BY RN; A B C D -- -- -- -- A B C D A1 A C1 D1 B1