asked on

find null and all the null column in one select query

Hi ,
I have a table table A having column col1,col2,col3,col4

I want to select the data in the table which has null values but with a statement which shows that what are the column having null values as below example:

INPUT:

col1 col2 col3
A B
X B
A

OUTPUT:

col1 col2 col3 statement
A B NULL IS COL2
X NULL IS COL1,COL3
A NULL IS COL2,COL3

noci

So you have to write out some queries....
(for 3 binary options: 8 queries: 2 ^ 3)

select * from A where NOT col1 IS NULL and NOT col2 IS NULL and NOT col3 IS NULL;
select * from A where col1 IS NULL and NOT col2 IS NULL and NOT col3 IS NULL ;
select * from A where NOT col1 IS NULL and col2 IS NULL and NOT col3 IS NULL;
select * from A where col1 IS NULL and col2 IS NULL and NOT col3 IS NULL ;
select * from A where NOT col1 IS NULL and NOT col2 IS NULL and col3 IS NULL;
select * from A where col1 IS NULL and NOT col2 IS NULL and col3 IS NULL ;
select * from A where NOT col1 IS NULL and col2 IS NULL and col3 IS NULL;
select * from A where col1 IS NULL and col2 IS NULL and col3 IS NULL ;

each of these queries will show their specific values.

Scott Pletcher

SELECT *,
STUFF('NULL IS' +
CASE WHEN col1 IS NULL THEN ',COL1' ELSE '' END +
CASE WHEN col2 IS NULL THEN ',COL2' ELSE '' END +
CASE WHEN col3 IS NULL THEN ',COL3' ELSE '' END, 8, 1, ' ') AS statement
FROM /*replace (...) with your table name*/ (
VALUES('A', NULL, 'B'),
(NULL, 'X', NULL),
('A', NULL, NULL),
('SHOULD', 'NOT', 'SELECT')
) AS test_data(col1, col2, col3)
WHERE col1 IS NULL OR col2 IS NULL OR col3 IS NULL

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial