Exclude columns with no data in them

Question

Using Oracle with TOAD.

I have a table that looks something like this (columns 2 and 4 are empty and columns 1, 3 and 5 have data in them):

column_1   column_2   column_3   column_4    column_5
a1                    b1                     c1
a2                    b2                     c2
a3                    b3                     c3
a4                    b4                     c4

I would like to do a simple select that excludes columns with no data in them (= columns 2 and 4) or ain other words, to select only the columns that have data in them. Is there a select command such as SELECT * FROM test_table WHERE columns ARE NOT NULL (this is pseudo code just for clarification for my problem).

The result should look like this:

column_1   column_3   column_5
a1         b1         c1
a2         b2         c2
a3         b3         c3
a4         b4         c4

Answer 1

You have to do a three-step approach but it is largely tedious but do-able in sqlplus

1)First identify the columns which are empty

2)Define the headers without those columns

3)define the body without those columns

    WITH data AS
        (
               SELECT '1' a,
                      ''  b ,
                      2   c ,
                      ''  d,
                      5   e
               FROM   dual
               UNION ALL
               SELECT '7' a,
                      ''  b ,
                      2   c ,
                      ''  d,
                      6
               FROM   dual
               UNION ALL
               SELECT '3' a,
                      ''  b ,
                      3   c ,
                      ''  d,
                      7
               FROM   dual
               UNION ALL
               SELECT '4' a,
                      ''  b ,
                      3   c ,
                      ''  d,
                      8
               FROM   dual
               UNION ALL
               SELECT '5' a,
                      ''  b ,
                      2   c ,
                      ''  d,
                      9
               FROM   dual),d1 AS
        (
                 SELECT   First_value(a) ignore nulls over (PARTITION BY a ORDER BY ROWNUM) ca,
                          first_value(b) ignore nulls over (PARTITION BY b ORDER BY ROWNUM) cb,
                          first_value(c) ignore nulls over (PARTITION BY c ORDER BY ROWNUM) cc,
                          first_value(d) ignore nulls over (PARTITION BY d ORDER BY ROWNUM) cd,
                          first_value(e) ignore nulls over (PARTITION BY e ORDER BY ROWNUM) ce
                 FROM     data
                 WHERE    ROWNUM=1 ),
        d2 as (SELECT 0 rw,
               CASE
                      WHEN ca IS NOT NULL THEN 'a'
                      ELSE ''
               END
                      ||chr(9)
                      ||
               CASE
                      WHEN cb IS NOT NULL THEN 'b'
                      ELSE ''
               END
                      ||chr(9)
                      ||
               CASE
                      WHEN cc IS NOT NULL THEN 'c'
                      ELSE ''
               END
                      ||chr(9)
                      ||
               CASE
                      WHEN cd IS NOT NULL THEN 'd'
                      ELSE ''
               END
                      ||chr(9)
                      ||
               CASE
                      WHEN ce IS NOT NULL THEN 'e'
                      ELSE ''
               END as DATA1
        FROM   d1
        UNION ALL
        SELECT 
               rownum rw,
               a
                      ||chr(9)
                      ||b
                      ||chr(9)
                      ||c
                      ||chr(9)
                      ||d
                      ||chr(9)
                      ||e
        FROM   data)
        select /*ansiconsole*/ DATA1
        from d2 order by rw asc;