Ignore rows when all column are null except the first one

Transact-SQL
1

Hell,

Here is my query sample.

select * from Test_Table

Result:

Item	A	B	C	D	E
12	2	8	NULL	0	9
14	7	6	1	4	NULL												
15	0	0	8	6	5																		
16	NULL	NULL	NULL	NULL	NULL												
17	12	7	2	1	9												
19	1	1	NULL	NULL	4													
20	NULL	NULL	NULL	NULL	NULL

I need a query to skip the rows when the column A, B, C, D, and E are NULL. In this case rows start with 16 and 20 are ignored. The new query will generate this result.

Item	A	B	C	D	E
12	2	8	NULL	0	9
14	7	6	1	4	NULL												
15	0	0	8	6	5				
17	12	7	2	1	9												
19	1	1	NULL	NULL	4

Thank you for your help.

2

Try this

select * from Test_Table
where A is not null and B is not null and C is not null and D is not null and E is not null
3

This may work
SELECT * FROm Test_Table
WHERE COALESCE(A, B, C, D, E) IS NOT NULL;

Note I did not test this and did it off the top of my head so it may be incorrect. :smile:

4

It is not working.

5

Thy this:

CREATE TABLE #X (Item INT, A INT, B INT, C INT, D INT, E INT);
INSERT INTO #X 
VALUES
(12,2,8,NULL,0,9),
(14,7,6,1,4,NULL),
(15,0,0,8,6,5),
(16,NULL,NULL,NULL,NULL,NULL),
(17,12,7,2,1,9),
(19,1,1,NULL,NULL,4),
(20,NULL,NULL,NULL,NULL,NULL);

SELECT * FROM #X

SELECT * FROM #X WHERE COALESCE(A,B,C,D,E) IS NOT NULL;

DROP TABLE #x;

Notice I have consumable input (INSERT INTO) and table definitions (CREATE TABLE) it helps us help you if you provide these.

Thanks

6

Following the general rule of not using functions against columns in WHERE or JOIN clauses, I'd write the condition this way:

WHERE NOT (A IS NULL AND B IS NULL AND C IS NULL AND D IS NULL AND E IS NULL)

7

You have a point. @madhivanan had the same thing.

8

Same functionally, but in my view more difficult to understand easily. But that's just my preference. Either method is certainly preferable to using a function on the columns.

9

Thanks djj55 for you time and help. Yes, Next time I will provide (INSERT INTO) and (CREATE TABLE) to make it easier.