|
Author's name: Frank Zhou Author's Email:zhou328@comcast.net |
Date written: 28th January 2006 Oracle version(s): 9.2 |
|
How do I collapse all nulls for every table column in a SQL Statement? |
|
The following SQL query pattern can be used 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
SQL> 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
SQL>
SQL> spool off;