Bobo (robertmurch (AT) gmail (DOT) com) wrote:
: I'm not sure if this requires a cursor or not.....any help would be
: appreciated.

: I have 60,000 records sorted in the order I imported them.

Does sqlserver quarantee a particular order of the rows based just on the
order of the rows as they were being imported (or the order they were
inserted)?


: If record one has a value in field one.... then I want to move to
: record two, and if it's field one is null then update it's field one
: with the value from field one in record one.

: I want to continue moving from record to record... updating it with
: the value from record one.......until I find the next field with a
: value in field one..... skip over that field and then fill THAT value
: down until it finds the next value etc.

: Example:

: Looks like this to start:

: Record FieldOne
: 1 ABC
: 2 NULL
: 3 NULL
: 4 NULL
: 5 DEF
: 6 NULL
: 7 NULL

: I WANT (and would appreciate ANY help) it to look like this:

: Record FieldOne
: 1 ABC
: 2 ABC
: 3 ABC
: 4 ABC
: 5 DEF
: 6 DEF
: 7 DEF

: Thank you for any info.

--

I'm not sure if this requires a cursor or not.....any help would be
appreciated.

I have 60,000 records sorted in the order I imported them.

If record one has a value in field one.... then I want to move to
record two, and if it's field one is null then update it's field one
with the value from field one in record one.

I want to continue moving from record to record... updating it with
the value from record one.......until I find the next field with a
value in field one..... skip over that field and then fill THAT value
down until it finds the next value etc.

Example:

Looks like this to start:

Record FieldOne
1 ABC
2 NULL
3 NULL
4 NULL
5 DEF
6 NULL
7 NULL

I WANT (and would appreciate ANY help) it to look like this:

Record FieldOne
1 ABC
2 ABC
3 ABC
4 ABC
5 DEF
6 DEF
7 DEF

Thank you for any info.

Here is one solution:

CREATE TABLE Foo (
keycol INT NOT NULL PRIMARY KEY,
datacol VARCHAR(10));

INSERT INTO Foo VALUES(1, 'ABC');
INSERT INTO Foo VALUES(2, NULL);
INSERT INTO Foo VALUES(3, NULL);
INSERT INTO Foo VALUES(4, NULL);
INSERT INTO Foo VALUES(5, 'DEF');
INSERT INTO Foo VALUES(6, NULL);
INSERT INTO Foo VALUES(7, NULL);

SELECT A.keycol, COALESCE(A.datacol, B.datacol) AS datacol
FROM Foo AS A
JOIN Foo AS B
ON A.keycol >= B.keycol
WHERE B.datacol IS NOT NULL
AND NOT EXISTS(SELECT *
FROM Foo AS C
WHERE C.keycol > B.keycol
AND C.keycol <= A.keycol
AND C.datacol IS NOT NULL);

/*

keycol datacol
----------- ----------
1 ABC
2 ABC
3 ABC
4 ABC
5 DEF
6 DEF
7 DEF

*/

--
Plamen Ratchev
http://www.SQLStudio.com

It's a text file that is being imported...... I am importing each line
in the order that they appear in the text file.
But I will never know what the values will be... and I will never know
how many blank records there are between each value.

On Jan 18, 11:49*am, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:
Wow! This should do the job.......
I really have to go back to reading my SQL books... i've never used
COALESCE before. So much to learn.

Thank you all SOOOOOOO much. I was trying to figure it out using a
cursor...... and I knew that was't the most efficient way.

Bobo (robertmurch (AT) gmail (DOT) com) wrote:

: > Does sqlserver quarantee a particular order of the rows based just on
the
: > order of the rows as they were being imported (or the order they were
: > inserted)?

: It's a text file that is being imported...... I am importing each line
: in the order that they appear in the text file.
: But I will never know what the values will be... and I will never know
: how many blank records there are between each value.

Whoops, I miss read "fieldOne" in your original post, I thought that
referred to the first column (which implied it was the index that was
sometimes missing). Now I realize you do have an index for each row, and
that makes it possible to detect the original order.