How not to display columns which are NULL in a view

Question

I've set up a view which combines all the data across several tables. Is there a way to write this so that only columns which contain non-null data are displayed, and those columns which contain all NULL values are not included?

ADDED: Sorry, still studying and working on my first big project so every day seems to be a new experience at the minute. I haven't been very clear, and that's partly because I'm not sure I'm going about things the right way! The client is an academic library, and the database records details of specific collections. The view I mentioned is to display all the data held about an item, so it is bringing together tables on publication, copy, author, publisher, language and so on. A small number of items in the collection are papers, so have additional details over and above the standard bibliographic details. What I didn't want was a user to get all the empty fields relating to papers if what was returned only consisted of books, therefore the paper table fields were all null. So I thought perhaps there would be a way to not show these. Someone has commented that this is the job of the client application rather than the database itself, so I can leave this until I get to that phase of the project.

Answer 1

CREATE VIEW dbo.YourView
AS
  SELECT (list of fields)
  FROM dbo.Table1 t1
  INNER JOIN dbo.Table2 t2 ON t1.ID = t2.FK_ID
  WHERE t1.SomeColumn IS NOT NULL
  AND t2.SomeOtherColumn IS NOT NULL

In your view definition, you can include WHERE conditions which can exclude rows that have certain columns that are NULL.

Update: you cannot really filter out columns - you define the list of columns that are part of your view in your view definition, and this list is fixed and cannot be dynamically changed......

What you might be able to do is us a ISNULL(column, '') construct to replace those NULLs with an empty string. Or then you need to handle excluding those columns in your display front end - not in the SQL view definition...

The only thing I see you could do is make sure to select only those columns from the view that you know aren't NULL:

SELECT (list of non-null fields) FROM dbo.YourView
WHERE (column1 IS NOT NULL) 

and so forth - but there's no simple or magic way to select all columns that aren't NULL in one SELECT statement...

Answer 2

In general, add a WHERE clause to your query, e.g.

WHERE a IS NOT NULL AND b IS NOT NULL AND c IS NOT NULL

Here, a b c are your column names.

If you are joining tables together on potentially NULL columns, then use an INNER JOIN, and NULL values will not be included.

EDIT: I may have misunderstood - the above filters out rows, but you may be asking to filter out columns, e.g. you have several columns and you only want to display columns that contain at least one null value across all the rows you are returning. Using dynamic SQL offers a solution, since the set columns varies depending upon your data.

Here's a SQL query that builds another SQL query containing the appropriate columns. You could run this query, and then submit it's result as another query. It assumes 'pk' is some column that is always non-null, e.g. a primary key - this means we can prefix additional row names with a comma.

SELECT CONCAT("SELECT pk"
   CASE (count(columnA)) WHEN 0 THEN '' ELSE ',columnA' END,
   CASE (count(columnB)) WHEN 0 THEN '' ELSE ',columnB' END,
   // etc..
   ' FROM (YourQuery) base')
FROM 
   (YourQuery) As base

The query works using Count(column) - the aggregate function ignores NULL values, and so returns 0 for a column consisting entirely of NULLs. The query builder assumes that YourQuery uses aliases to ensure there no duplicate column names.

While you cant put this into a view, you could wrap it up as a stored procedure that copies the data to another table - the result table. You may also set up a trigger so that the result table is updated whenever the base tables change.

Answer 3

There is no way to do this in sql.

Answer 4

If we're reading your question right, there won't be a way to do this in SQL. The output of a view must be a relation - in (over-)simplified terms, it must be rectangular. That is, each row must have the same number of columns.

If you can tell us more about your data and give us some idea of what you want to do with the output, we can perhaps offer more positive suggestions.

Answer 5

You cannot do this in a view, but you can do it fairly easily using dynamic SQL in a stored procedure.

Of course, having a schema which shifts is not necessarily good for clients who consume the data, but it can be efficient if you have very sparse data AND the consuming client understands the varying schema.

If you have to have a view, you can put a "header" row in your view which you can inspect client-side on the first row in your loop to see if you want to not bother with the column in your grid or whatever, you can do something like this:

SELECT * FROM (
    -- This is the view code
    SELECT 'data' as typ
           ,int_col
           ,varchar_col
    FROM TABLE

    UNION ALL

    SELECT 'hdr' as typ
           -- note that different types have to be handled differently
           ,CASE WHEN COUNT(int_col) = 0 THEN NULL ELSE 0 END
           ,CASE WHEN COUNT(varchar_col) = 0 THEN NULL ELSE '' END
    FROM TABLE
) AS X
-- have to get header row first
ORDER BY typ DESC -- add other sort criteria here

Answer 6

I suspect what's going on is that an end user is running CrystalReports and complaining about all the empty columns that have to be removed manually.

It would actually be possible to create a stored procedure that would create a view on the fly, leaving out dataless columns. But then you would have to run this proc before using the view.

Is that acceptable?