Geek Answers Handbook

How to not display columns that are NULL in the view

I created a view that combines all the data in several tables. Is there a way to write this so that only columns containing non-zero data are displayed, and those columns that contain all NULL values ​​are not included?

ADDED: Sorry, I'm still studying and working on my first big project, so every day, it seems, is a new experience per minute. I was not very clear, and partly because I was not sure that I was going to make the right path! The client is an academic library, and the database records the details of specific collections. The view I mentioned is to display all the data stored in the element, so it combines tables for publication, copying, author, publisher, language, etc. A small number of items in the collection are documents, so they have additional information, in addition to standard bibliographic data. I did not want the user to receive all empty fields related to documents, if the return only consisted of books, so the fields in the paper table were empty.So I thought there might be a way to show this. Someone commented that this is the task of the client application, and not the database itself, so I can leave it until I get to this phase of the project.

+5
source share
6 answers

There is no way in sql to do this.

+3
source 
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 the definition of your view, you can include WHERE clauses that can exclude rows with specific columns that are NULL.

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

What you could do is build a ISNULL(column, '')construct to replace these NULLs with an empty string. Or then you need to handle the exception of these columns in your front display interface, and not in the definition of the SQL view ...

, , , , , , NULL:

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

.. - , NULL SELECT...

+1

WHERE ,

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

b c - .

NULL, INNER JOIN, NULL .

: , - , , . , , . SQL , .

SQL-, SQL-, . , . , "pk" - , , . - , .

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

Count (column) - NULL 0 , NULL. , YourQuery , .

, , - . , , .

0

, SQL . - in (over-) , . , .

, , .

0

, , SQL .

, , , , , , - .

, "", , , - , - :

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
0

I suspect that the end user is starting CrystalReports and complaining about all empty columns that need to be removed manually.

In fact, it would be possible to create a stored procedure that would create a view on the fly, leaving the columns without data. But then you will need to run this proc before using the view.

It is acceptable?

0
source

More articles:


All Articles