Hide Empty columns

I got a table with 75 columns,. what is the sql statement to display only the c开发者_如何学JAVAolumns with values in in ?

thanks

It's true that a similar statement doesn't exist (in a SELECT you can use condition filters only for the rows, not for the columns). But you could try to write a (bit tricky) procedure. It must check which are the columns that contains at least one not NULL/empty value, using queries. When you get this list of columns just join them in a string with a comma between each one and compose a query that you can run, returning what you wanted.

EDIT: I thought about it and I think you can do it with a procedure but under one of these conditions:

• find a way to retrieve column names dynamically in the procedure, that is the metadata (I never heard about it, but I'm new with procedures)

• or hardcode all column names (loosing generality)

You could collect column names inside an array, if stored procedures of your DBMS support arrays (or write the procedure in a programming language like C), and loop on them, making a SELECT each time, checking if it's an empty* column or not. If it contains at least one value concatenate it in a string where column names are comma-separated. Finally you can make your query with only not-empty columns!

Alternatively to stored procedure you could write a short program (eg in Java) where you can deal with a better flexibility.

*if you check for NULL values it will be simple, but if you check for empty values you will need to manage with each column data type... another array with data types?

I would suggest that you write a SELECT statement and define which COLUMNS you wish to display and then save that QUERY as a VIEW.

This will save you the trouble of typing in the column names every time you wish to run that query.

As marc_s pointed out in the comments, there is no select statement to hide columns of data.

You could do a pre-parse and dynamically create a statement to do this, but this would be a very inefficient thing to do from a SQL performance perspective. Would strongly advice against what you are trying to do.

A simplified version of this is to just select the relevant columns, which was what I needed personally. A quick search of what we're dealing with in a table

SELECT * FROM table1 LIMIT 10;

-> shows 20 columns where im interested in 3 of them. Limit is just to not overflow the console.

SELECT column1,column3,colum19 FROM table1 WHERE column3='valueX';

It is a bit of a manual filter but it works for what I need.