Removing null in columns

Question

Removing null in columns

raja_saminathan

SSCommitted

Points: 1607
More actions
May 30, 2008 at 12:25 am

#187201

HI all
I have a Table like the following and I want to remove the null value columns from the table.
Is there any possibility of querying without loops
/* Input
IdValueDescription
1 75NULL
1 NULLread
280NULL
2NULLwrite
*/
Declare @Table table(Id int,Value int, Description varchar(20))
insert into @Table
select 1,75,null union
select 1,null,'read' union
select 2,80,null union
select 2,null,'write'
select * from @Table
/* expected output
Idvalue description
1 75 read
280 write
*/
Thanks and Regards,
Rajesh

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Solution depends on whether the data sample you supplied is representative. Try this and if it doesn't work as you expected, give us new sample data that will reflect all possibilities.

select id, MAX(value), MAX(description)

from @Table

group by id

However, you wrote in the beginning that you want to "remove NULLs from the table", but this only removes NULLs from the resultset, not from the table... and it is a fact that if your table really contains such entries, something should be done about it - normalize existing data, and make sure that any new data will be inserted correctly.

preetha.sathyamoorthy Ten Centuries Points: 1180 More actions · Answer 2

You can use ISNULL() to do this.

For example,select ISNULL(Value, 0) , ISNULL(Description,0) from @Table

raja_saminathan SSCommitted Points: 1607 More actions · Answer 3

Thanks Vladan,

It worked Fine and i got the expected result,

is there is any other way without doing group by Id to move the column values upwards by using coalesce or some other function?

Rajesh

raja_saminathan SSCommitted Points: 1607 More actions · Answer 4

Hi Preetha,

Your Query will Replace the Null Values by 0 and not the expected Result.

Thanks for your Support and taking time to help me.

Rajesh

Vladan SSC-Insane Points: 22072 More actions · Answer 5

As far as I know, there isn't. COALESCE and ISNULL work when doing this thing on a row /like COALESCE (home_phone_no, office_phone_no)/, while aggregation is needed when you do the same on a column. Actually, I think it is pretty self-explanatory... in your case, you are not only removing a NULL, you are in fact merging two rows into one - so there has to be some GROUP BY.