Filling missing values

[jluckhoff]

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

[davey23uk]

select col1,'|', col2 from table order by col1, col2; ?

[jluckhoff]

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

[jluckhoff]

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