1

I have to write a query to get the following data as result. I have four columns in my database. ID is not null, all others can have null values.

 EMP_ID    EMP_FIRST_NAME    EMP_LAST_NAME    EMP_PHONE

 1         John              Williams         +123456789 
 2         Rodney                             +124568937
 3                           Jackson          +124578963 
 4         Joyce             Nancy

Now I have to write a query which returns the columns which are not null. I do not want to specify the column name in my query.

I mean, I want to use SELECT * FROM TABLE WHERE - and add the filter, but I do not want to specify the column name after the WHERE clause.

This question may be foolish but correct me wherever necessary. I'm new to SQL and working on a project with c# and sql.

Why I do not want to use the column name because, I have more than 250 columns and 1500 rows. Now if I select any row, at least one column will have null value. I want to select the row, but the column which has null values for that particular row should not appear in the result.

Please advice. Thank you in advance.

Regards, Vinay S

Improve this question
4
  • 8
    I think that if you have 250 columns in a table and want to filter which ones you get depending on their value, then you don't have a properly normalized database. Commented Jul 26, 2011 at 19:20
  • You want to write a query that returns you the columns that are not null for what condition?, all your table?, you mean that the column doesn't have a single null value?. Also, does your table have 4 columns or 250? Commented Jul 26, 2011 at 19:26
  • 2
    Agree with Anders. The number of rows has no bearing on your question, but 250 columns suggests you have failed to design your table correctly. Your hypothetical probably has an absurdly difficult solution, but refactoring and normalizing your table would be less time-consuming immediately and down the road. Commented Jul 26, 2011 at 19:27
  • Ok, I understand the complexity of my question now. Please advise what is the best design for the below data. I have 10 object information to be stored in a database. The ten objects may have or may not have individual types inside them. Object 1 may have 4 types. Say Object 1, type 1 has 24 parameters, and values. Object 1, type 2 may have only 12 parameters and values. Object 2 may have 72 parameters and values. So, for all these objects, I get more than 250+ parameters and the value may be present or null. All are string data. Please advice how do I represent this in a database. Thank you. Commented Jul 27, 2011 at 3:56

3 Answers 3

Reset to default
5

Every row returned from a SQL query must contain exactly the same columns as the other rows in the set. There is no way to select only those columns which do not return null unless all of the results in the set have the same null columns and you specify that in your select clause (not your where clause).

To Anders Abels's comment on your question, you could avoid a good deal of the query complexity by separating your data into tables which serve common purposes (called normalizing).

For example, you could put names in one table (Employee_ID, First_Name, Last_Name, Middle_Name, Title), places in another (Address_ID, Address_Name, Street, City, State), relationships in another, then tiny 2-4 column tables which link them all together. Structuring your data this way avoids duplication of individual facts, like, "who is John Williams's supervisor and how do I contact that person."

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

Comments

3

Your question reads:

I want to get all the columns that don't have a null value.

And at the same time:

But I don't want to specify column names in the WHERE clause.

These are conflicting goals. Your only option is to use the sys.tables and sys.columns DMVs to build a series of dynamic SQL statements. In the end, this is going to be more work that just writing one query by hand the first time.

Improve this answer

Comments

3

You can do this with a dynamic PIVOT / UNPIVOT approach, assuming your version of SQL Server supports it (you'll need SQL Server 2005 or better), which would be based on the concepts found in these links:

Effectively, you'll select a row, transform your columns into rows in a pivot table, filter out the NULL entries, and then unpivot it back into a single row. It's going to be ugly and complex code, though.

Improve this answer

Comments

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.