SQL Server Select Query

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

---

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

---

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.

---

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:

• Dynamic Pivot
• PIVOT / UNPIVOT

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.