Forum Stats

  • 3,757,630 Users
  • 2,251,251 Discussions
  • 7,869,876 Comments

Discussions

Select not null columns from a table

Soofi
Soofi Member Posts: 476 Blue Ribbon

Hi All,

I need to generate a report which displays the columns which has data.

Here is my example,



So my sample table has num1 ... num3 and col1.. col3. And you can see there is completely no data for the columns Num2 and Col2 which I need to exclude while generating the report.

My final output should be like this


NOTE - The above case is just a sample, actual data and number of columns will vary.

What will be best way to achieve this.

I am using Oracle Database 12c (12.2.0.1.0 )

Thanks in advance,

Soofi

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,031 Red Diamond
    edited Dec 24, 2020 3:03PM

    Hi,

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data. Always say which version of Oracle you're using (e.g. 12.2.0.1.0), as you did.

    See: How to Ask Questions in Developer Community Spaces - oracle-tech


    The number of columns must be hard-coded into the query. If you want which columns are included in the query to depend on the data fetched by the query, then you need dynamic SQL.

    What is the business requirement you need to meet here? Do you really need a variable number of columns? Would you accept a result set that always had six columns, but the ones with all NULLs were pushed to the right?

  • jflack
    jflack Member Posts: 1,516 Bronze Trophy
    edited Dec 24, 2020 3:32PM

    When you're talking variable numbers of columns, you are in the territory of dynamic sql, and you are going to have to write some PL/SQL. So you need to change your select based on what is or is not in the columns. I'd start with a select like

    DECLARE
      num1_count INTEGER;
      num2_count INTEGER;
      num3_count INTEGER;
      col1_count INTEGER;
      col2_count INTEGER;
      col3_count INTEGER;
      select_cmd VARCHAR2(1000);
    BEGIN
    SELECT sum(CASE WHEN num1 IS NULL THEN 0 ELSE 1 END) as num1_count,
           sum(CASE WHEN num2 IS NULL THEN 0 ELSE 1 END) as num2_count,
           sum(CASE WHEN num3 IS NULL THEN 0 ELSE 1 END) as num3_count,
           sum(CASE WHEN col1 IS NULL THEN 0 ELSE 1 END) as col1_count,
           sum(CASE WHEN col2 IS NULL THEN 0 ELSE 1 END) as col2_count,
           sum(CASE WHEN col3 IS NULL THEN 0 ELSE 1 END) as col3_count
      INTO num1_countl,num2_count,num3_count,col1_count,col2_count,col3_count
      FROM MY_TABLE; /* add any WHERE clause you will want on the final SELECT */
    select_cmd := 'SELECT ';
    IF num1_count > 0 THEN select_cmd := select_cmd || 'num1,'; END IF;
    IF num2_count > 0 THEN select_cmd := select_cmd || 'num2,'; END IF;
    /* continue like this until you added to the SELECT all the columns that have some NOT NULLS */
    /* delete that last comma and add your WHERE and ORDER BY clauses */
    
    

    The next thing you'll need is a OPEN FOR to get a cursor for the SELECT that you build. But that can wait.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,031 Red Diamond

    Hi, @Soofi

    If you do need to use dynamic SQL, your front end may have tools that help. In SQL*Plus, for example, you can use substitution variables, like this:

    -- Preliminary Query
    COLUMN col_list_col NEW_VALUE col_list
    
    SELECT RTRIM (  MIN (NVL2 (num1, 'NUM1, ', NULL))
                 || MIN (NVL2 (num2, 'NUM2, ', NULL))
                 || MIN (NVL2 (num3, 'NUM3, ', NULL))
                 || MIN (NVL2 (col1, 'COL1, ', NULL))
                 || MIN (NVL2 (col2, 'COL2, ', NULL))
                 || MIN (NVL2 (col3, 'COL3, ', NULL))
    	     , ', '
    	     ) AS col_list_col
    FROM  table_x
    ;
    
    -- Main Query
    SELECT &col_list
    FROM   table_x;
    


  • mathguy
    mathguy Member Posts: 10,069 Blue Diamond

    What is the desired output if there are no non-NULL values in ANY column of the table? For example, if the table has no rows at the time of execution?

  • Soofi
    Soofi Member Posts: 476 Blue Ribbon

    Thanks for your replies.

    So speaking about my business requirement, I am going to use this table as a generic open table with 300 columns.

    200- varchar (col1..col200)

    50-numeric (num1..num50)

    50-date columns. (date1..date50)

    Data will get inserted into this table from different procedures across the application with key identifier for each insert.

    At any point of time for any record not all 300 columns will get populated.

    I need to pull the records from this table using the key identifier with specific columns which have at least one record.

    So I really need variable number of columns.


    Rather than writing complex select statement, I can handle the code in a package/procedure and insert the result into my table and throw it to an excel template to get the output with only populated columns.

    At present I don't have any table scripts or inserts with me. If you guys have better idea to achieve this let me know.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,031 Red Diamond
    edited Dec 26, 2020 4:33PM

    Hi, @Soofi

    What is the business requirement here? What you described sounds like a proposed way to meet some business requirement. What is that requirement? What are you trying to do? What purpose does the 300-column table serve? Why are NULL columns a problem? Say half of the columns are always NULL; that still leaves you with 150 columns. How are you planning to use that 150-column result set? Why can't you use the 300-column table the same way?

    If you really do need to produce a result set with a variable number of columns, the way I showed in the 3rd reply above will work as long as the list of column names (including delimiters) is not longer than 4000 bytes. If you end up selecting all 300 columns, that means the column names can average 12 bytes, using a single comma as a delimiter.

  • jflack
    jflack Member Posts: 1,516 Bronze Trophy

    Have to agree with @Frank Kulash. This sounds like a disaster waiting to happen. Make tables that have the columns you need NOW, and give them names to say for future developers what they contain. I've seen BAD things happen when database designers try to get TOO generic. Don't try to future proof it. Change happens - LET IT happen as needed, when needed.

    Okay - so you won't listen to us or your users won't listen to you. Let me suggest that IF you MUST, create a single text column with an IS JSON constraint, and store JSON in it. JSON in the database can give you the flexibility your are looking for and fairly easy to query JSON items as if they were relational columns.

    BEDEPrze-moC