The Oracle (tm) Users' Co-Operative FAQ

How do I collapse all nulls for every table column in a SQL Statement?


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?

Back to index of questions


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;


 

Back to top

Back to index of questions