SQL Query to exclude the null

Dear expert,

Simple query, I want MSSQL to exclude all the NULL result in columns from the table.

Any easy solution?
Thx
LVL 1
WeTiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MishaProgrammerCommented:
select * from YourTableName WHERE NOT YourColumn IS NULL

Open in new window

0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I want MSSQL to exclude all the NULL result in columns from the table.
how you want to do the comparison? exclude them and ... replace with some values? or exclude the rows if any column got NULL value?

pls provide an example.
0
WeTiAuthor Commented:
WHERE NOT YourColumn this YourColumn, you need to spec all the columns, ok I know this already, but I want to show all columns, this table got around 60s columns, some of them are null some not. How do i exclude all the columns that is null?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
How do i exclude all the columns that is null?

do you want to exclude the record (row) or columns??
0
WeTiAuthor Commented:
Exclude the columns when the results is null, this only apply for result that showing one row, if more rows then it wouldn't work I know.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Exclude the columns when the results is null, this only apply for result that showing one row, if more rows then it wouldn't work I know.

It would be great and easier if you could post the query and explain which columns you don't want NULL values to be displayed..
If you want the difficult approach, then follow the steps below..
1. Add only NOT NULL columns in your SELECT clause.
2. Don't do LEFT JOIN in your SELECT query as it would bring NULL values in few columns referenced from that table.
3. Don't use Subqueries as it might bring NULL values.
4. Don't use Scalar or Table valued functions as it might bring NULL Values.

>> I want to show all columns, this table got around 60s columns, some of them are null some not. How do i exclude all the columns that is null?

Then you might need to add the condition for all NULL able columns in the WHERE condition like this
WHERE nullable_col1 IS NOT NULL and nullable_col2 IS NOT NULL 

Open in new window

0
WeTiAuthor Commented:
Here is the exemple:

Name | Address | Log | info | number | system
WeTi   | null       | 2     | 2      | null         | null

Now i want to exclude the address and number and system columns, this is a easy result, my result got 50+ columns more, Im only interested in those columns that have information in it.  How do i do?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Now i want to exclude the address and number and system columns

Okay, if that is the case, then you can follow my first comment mentioned.. Seems address, number and system columns are Nullable and hence remove all those Nullable columns in your WHERE clause..
1. Add only NOT NULL columns in your SELECT clause.
0
WeTiAuthor Commented:
You mean this?
WHERE nullable_col1 IS NOT NULL and nullable_col2 IS NOT NULL
this means I need to add 20+ columns here...
0
slightwv (䄆 Netminder) Commented:
Do you physically want to remove the columns from the result set?

Using your example above you want the columns returned as:
Name | Log | info
WeTi   |  2     | 2

Or do you not want the ROW returned?

What if you have data like:
Name | Address | Log | info | number | system
WeTi   | null       | 2     | 2      | null         | null
null   | 2       | null     | 2      | null         | null


Dynamic columns in a result set is quite messy.  If you can provide the requirements behind this request, we might be able to offer alternative solutions.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
>>Here is the exemple:
 Name | Address | Log | info | number | system
 WeTi   | null       | 2     | 2      | null         | null
<<

I think what you are asking may not be possible.
So, in your example you would only want to return Name, Log, and info?
What if there is another record like -
 WeTi   | 123 Main St.       |null     | null      | 5         | null
How would you only return Name, Address, and number for this record while returning different columns for the first record?

The only possibility I see is that you could select columns omitting the ones where EVERY value is null. This would have to be done dynamically to test which columns would qualify (or not), but it could be done. Perhaps you could provide some sample data (maybe pick 10 or so columns) and what you would expect as the output from that data. It would help in testing any solutions.
0
WeTiAuthor Commented:
Yes as Awking00 says it's not possible if that is the case, but in my case I only get one row in result and want to remove all the null in all the columns of the result if there is one.
0
slightwv (䄆 Netminder) Commented:
Please confirm what I asked above:

Using your example above you want the columns returned as:
Name | Log | info
WeTi   |  2     | 2


So, 1 row with three columns.

The number of columns will be dynamic.
0
Mark WillsTopic AdvisorCommented:
Indeed the number of columns will be dynamic, such that from row to row it will be indistinguishable, unless of course, some kind of metadata / tags (like XML) to identify which column is which....

But WeTi has also said he only wants one row....

Will watch with great interest and think I can help.

Will probably need to be a stored procedure - with parameter to identify the unique row.

Maybe an example (filling in the <?> with meaningful names) :  select <columns> from <table name> where <conditions>

Adding a bit more to :
 Name | Address | Log | info | number | system
 WeTi   | null       | 2     | 2      | null         | null

Is there some DDL / table definition ?

Will await greater clarification from WeTi
0
WeTiAuthor Commented:
Name | Address | Log | info | number | system
 WeTi   | null       | 2     | 2      | null         | null

This is only a exemple of how result select query table looks like in database. For me to know those null columns is not interesting at all for me to know, and I need to scroll loooong to the left or right to find more info that interest me. This is why I would like to know how to do it... I think why and why was irrelevant here anyway, now I only want to know the:
Name | Log | info
WeTi   |  2     | 2
Address, number, system exclude. now I can as i know select only those columns in the select name, log, info, to exclude those info yes however we got like 10000 tables and all of them looks diffirent from each other... this is a headache for me to check which columns are there and exclude them.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> we got like 10000 tables and all of them looks diffirent from each other... this is a headache for me to check which columns are there and exclude them.

Since we don't have much details about your database, I'm not sure about the below items.
1. why your Database is designed to have around 10000 tables like this.
2. why you need to SELECT only 1 record from the table with all columns with NULL values to be hidden or not displayed

Since we don't know the background of your requirement, I would suggest to go with below approach to get your requirement completed faster:
1. Create a simple application with dropdown of all your 10000 table names.
2. Once you select the table name, it will select only 1 record from the table by running
SELECT TOP 1 * from ur_tablename

Open in new window

3. Once this result is in your dynamic dataset, check what are all the columns having NULL values and then just disable it out.
4. Display only the columns that have values.

As explained above, your requirement can be handled better at the application level instead of writing tedious scripts at SQL Server level.
Kindly check it and let us know for more details.
0
awking00Information Technology SpecialistCommented:
>>Yes as Awking00 says it's not possible if that is the case, but in my case I only get one row in result and want to remove all the null in all the columns of the result if there is one. <<
I assume your 10000 tables each have multiple rows. How do you get only one row? Are you selecting something with a where clause that guarantees that? It would still be nice if you could provide some sample data of maybe two tables, with 8-10 columns each and 10-20 rows, with the one row for each table that you would get as a result and how you would want it to display. If you help us understand better, we can help you better.
0
WeTiAuthor Commented:
... ok the system is designed like this, a membership system, address info field got 3 types, address 1 2 3, only 1 is used, I dont know why, I didnt design this system I only draw the information from it. Now As I said, table 1, got: surname, lastname, address 1 2 3, systeminfo 1 2 3 4 5 6, now those 123 and 123456 are columns some with informations some are not, I got do is:
select * tablename where id='33332145' this query will show all the columns in that table with that ID, now I don't want to see adress 2 3 and systeminfo 1 2 3 4 5 6 that are nulls I can work around this by: select surname, lastname, address 1, etc etc, and save this query so i can use it again, my question is: is there a smarter way doing those like: SQL show me ID: 33332145 and all columns that is NOT NULL
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> my question is: is there a smarter way doing those like: SQL show me ID: 33332145 and all columns that is NOT NULL

Yes, you might need to write a small application as suggested in my earlier comment..
Kindly check it once and let us know for any feedbacks..
0
WeTiAuthor Commented:
Wish to close it now, seems no 100% correct answer.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.