Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 find columns with null value for a specific rowid

Author  Topic 

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-08-26 : 09:47:37
Say i have 1 table having 10 columns out of which Col1 is unique and Col 3 to Col9 can be nullable columns.
If i pass RowId= 5 then it shud see the rowid for which Col3 to Col9 have NULL data and display those null column names to me.

scoo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-26 : 11:11:55
what if col3 to col9 is not null for rowid=5?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-08-26 : 12:28:57
if no col is null then we ll not show the anything but if any of the col is null we ll take that col name and display.
eg:
col 4 is having null value for a rowid 4 and rest of the col's are not null for this id then we ll display col4 name to the user that on this id col4 is null and by this user ll get to know which col is empty and he will reenter value for that col.


scoo
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-26 : 12:50:26
Something to help you to start with:

Assuming that max 1 column will be null.

Create table CheckNullInCol
(
Id int ,
Col1 varchar(50),
Col2 varchar(50),
Col3 varchar(50),
Col4 varchar(50),
Col5 varchar(50)
)

Insert into CheckNullInCol
Select 1,'Test1','Test2','Test3',null,'Test5' union
Select 2,'Test1','Test2','Test3','Test4','Test5' union
Select 3,'Test1','Test2','Test3','Test4','Test5' union
Select 4,'Test1','Test2',Null,'Test4','Test5' union
Select 5,'Test1','Test2',Null,Null,Null

--select * from CheckNullInCol

Select ID,
case
when col3 is null then 'Col3'
when col4 is null then 'col4'
when col5 is null then 'col5'
End As NullColumn
From CheckNullInCol
Where col3 is null or col4 is null or col5 is null
Go to Top of Page

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-08-26 : 13:43:54
this is working but what if i have more then one column which are null.
right now i am getting only one column name but what if i have for eg: 5 columns which have null value . I want to show all the columns which have null values ie. all columns with null values in different columns. so that i can use this column names in my application .

scoo
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-27 : 00:31:27
Try this:

Select ID,
Case when Col3 is null then 'Col3,' Else '' End +
Case when Col4 is null then 'Col4,' Else '' End +
Case when Col5 is null then 'Col5,' Else '' End
As NullColumn
From CheckNullInCol
Where col3 is null or col4 is null or col5 is null

Regards,
Bohra
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-28 : 01:54:32
i think what you're talking is more of front end issue. you can retrieve the columns as it is from table and in your front end app check for nulls and then hide those columns from display

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -