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
lishaAsked:
Who is Participating?
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.

nociSoftware EngineerCommented:
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 DBACommented:
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
slightwv (䄆 Netminder) 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

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
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
Databases

From novice to tech pro — start learning today.