find null and all the null column in one select query

lisha
lisha used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
nociSoftware Engineer
Distinguished Expert 2018

Commented:
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 PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Same idea as Scott but in Oracle syntax:
select col1, col2, col3,
	rtrim('NULL IS '
		|| case when col1 is null then 'COL1,' end
		|| case when col2 is null then 'COL2,' end
		|| case when col3 is null then 'COL3,' end,',')
from tab1 where col1 is null or col2 is null or col3 is null
/

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial