SQL Query that fill in the blanks

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 2000 Forums

SQL Server Development (2000)

SQL Query that fill in the blanks

Author

Topic

jluckhoff
Starting Member

4 Posts

Posted - 2004-08-15 : 10:07:21

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

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-15 : 10:28:19

SELECT T.Col1, IsNull(T.Col2, (SELECT TOP 1 Col2 FROM TABLE WHERE Col1 = T.Col1 AND Col2 IS NOT NULL))
FROM Table T

jluckhoff
Starting Member

4 Posts

Posted - 2004-08-15 : 10:46:39

Thanks Timmy, but your solution unfortunately gives me the same result as I have been getting namely this:

Col1 ¦ Col2
---------------
CC1 ¦ CC3
CC1 ¦ CC3
CC1 ¦ CC3
CC1 ¦ CC999
CC1 ¦ CC3
CC1 ¦ CC3
CC2 ¦ CC3
CC2 ¦ CC3
CC2 ¦ CC3
----------------

The statment seems to ignore the fact that "CC999" should be repeated in there as well.

I'm begining to wonder if this is at all possible without adding more info, like maybe using a temp table to load the data in and add an ID column of some sort.

Any ideas?

j

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-15 : 10:54:01

Sorry - didn't get the jist straight away - it's late...

Is there another column that is used to order the table? If not, then you have a problem because the database may be ordering it differently. If there is an 'ordering' column, then you could possibly do it:
change the sub-query to this:
SELECT TOP 1 Col2 FROM TABLE WHERE Col1 = T.Col1 AND Col2 IS NOT NULL AND keyID < T.keyID ORDER BY keyID DESC
A bit long-winded but it should do the trick with some experimentation.

jluckhoff
Starting Member

4 Posts

Posted - 2004-08-15 : 10:56:54

Thanks Timmy,

I dont have an order id in there but I think I might need to do that then.
I'll give it a bash

Thanks for the help.

j

jluckhoff
Starting Member

4 Posts

Posted - 2004-08-15 : 10:57:15

Thanks Timmy,

I dont have an order id in there but I think I might need to do that then.
I'll give it a bash

Thanks for the help.

j

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources