2

I have a table with 70 plus columns. I often search the table for a single row.

Ex:

select * from customer where customer_id='xyz';

The customer_id is unique.

In the result, there will most probably be 30 to 40 columns with null value.

I am always interested only in the non-null fields.

Is there a way in SQL to list out only those columns that are non-null?

PS: Unfortunately, the non-null fields will not be the same fields for all customers. Say, If 'Customer A' has null in 'Column R', 'Customer B' might have a valid value in that column. And lastly, my query will always be focused on only 1 customer at any given time.

Brief example:

query: 

select * from customer where cust_id='826122';

actual result:

cust_id - 826122

cust_fname - martha

cust_lname - kane

cust_alt_add - null

cust_cell_acode - 210

cust_home_phone - null

expected result: (I don't want the columns with null values to appear in my result)

cust_id - 826122

cust_fname - martha

cust_lname - kane

cust_cell_acode - 210

Improve this question
6
  • 1
    Show us an example of your data and what you want the query output to be. Commented Apr 15, 2013 at 22:45
  • 2
    IS there a way.... The answer is yes: one method would be to write a wrapper procedure that you call. It obtains the results of the SQL iterates though each column builds a CTE with the columns which have data selects from the CTE and returns that result set. Why one would want to do this though... since your change the results requested (Select *) isn't doing a * anymore... is beyond me. Commented Apr 15, 2013 at 22:48
  • 3
    If you are running this query from .net, java, coldfusion or something similar, you might be better off using application code to achieve your goal. Commented Apr 15, 2013 at 22:58
  • 2
    Welcome to Stack Overflow! Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it. Commented Apr 16, 2013 at 0:42
  • I have added the tags now and have also added a brief example. Commented Apr 16, 2013 at 18:40

1 Answer 1

Reset to default
3

You can do something like

SELECT * FROM Customer WHERE customer_id = 'xyz' AND some_col IS NOT NULL;

Or

SELECT * FROM Customer WHERE customer_id IS NOT NULL

Hope that helps.

EDIT

After trying to find a suitable answer, it seems that there are very few easy ways to do what you want to do. I think that you best shot is using answers from this post.

Though, the question is slightly different, I think you may find that those answers are helpful. I apologize for not being able to give you the answer you were looking for.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This is not what is desired here.
I simply assumed that the question was the bold text, please correct me if my assumption is incorrect, and I will adjust my answer. I apologize for any inconvenience.
@David: Thanks for responding but I was actually looking for help to filter out all the non-null columns from my result. (and I wouldn't know which ones would be null beforehand). I will edit my question to add a brief example.
@Ramnath Ahh yes, I see, let me edit and give you a better answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.