| Author |
 Topic  |
|
|
jluckhoff
Starting Member
4 Posts |
 Posted - 08/15/2004 : 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
Flowing Fount of Yak Knowledge
Australia
1242 Posts |
Posted - 08/15/2004 : 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 - 08/15/2004 : 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
Flowing Fount of Yak Knowledge
Australia
1242 Posts |
Posted - 08/15/2004 : 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 - 08/15/2004 : 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 - 08/15/2004 : 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 |
|
|
| |
Topic |
|
SQL Query that fill in the blanks
