Post reply

Removing null in columns

  • 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

    Id Value Description

    1 75 NULL

    1 NULL read

    2 80 NULL

    2 NULL write

    */

    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

    Id value description

    1 75 read

    2 80 write

    */

    Thanks and Regards,

    Rajesh

  • May 30, 2008 at 12:58 am

    #822124

    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.

  • May 30, 2008 at 1:13 am

    #822127

    You can use ISNULL() to do this.

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

  • May 30, 2008 at 1:26 am

    #822129

    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

  • May 30, 2008 at 1:29 am

    #822131

    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

  • May 30, 2008 at 1:33 am

    #822132

    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.

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

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