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

There is no way to do this in sql.

Answer 2

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...