Filling missing values
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Filling missing values

  1. #1
    Join Date
    Mar 2003
    Posts
    29

    Filling missing values

    Hi,

    I have a sticky one here - well at least for me.

    Say I have the following table:

    Col1 Col2
    ----------
    CC1 CC3
    CC1 NULL
    CC1 NULL
    CC1 CC999
    CC1 NULL
    CC1 NULL
    CC2 CC3
    CC2 NULL
    CC2 NULL
    ----------

    Is there a way in SQL to display it as follows:



    Col1 Col2
    ----------
    CC1 CC3
    CC1 CC3
    CC1 CC3
    CC1 CC999
    CC1 CC999
    CC1 CC999
    CC2 CC3
    CC2 CC3
    CC2 CC3
    ----------
    Any comments would be much appreciated.

    Thanks,

    j

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    select col1,'|', col2 from table order by col1, col2; ?

  3. #3
    Join Date
    Mar 2003
    Posts
    29
    Sorry davey23uk

    That just reorders them - I need to fill the null values as demonstrated in my post.

    I'm beginning to doubt if this can be done without adding more info to the table, maybe load it into a temp table and add an id column to it.

    Any ideas?

    j

  4. #4
    Join Date
    Mar 2003
    Posts
    29
    OK

    Got it.

    I added an identity column to the table and then the following statement allows you get the result:


    SELECT t.orderid, T.Col1, IsNull(T.Col2, (SELECT TOP 1 Col2 FROM Table
    WHERE Col1 = T.Col1 AND Col2 IS NOT NULL AND ROWID < T.ROWID
    ORDER BY ROWID DESC))
    FROM Table T

    My thanks to Timmy from OZ for his help on this.

    j

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width