CODEWITHSUNDEEP
sqlt-sql
abaldwin99
abaldwin99

Reputation: 913

Concatenate data if null

I would like ROW_Number() to work normally UNLESS column 'box' is Null. If 'box' is null the row number doesn't increase.

I have data that looks like this...

Row Box
1   5
2   3
3   1
4   Null
5   Null
6   2
7   8
8   Null
9   Null

I want my query to pull out data that looks like this...

Row Box
1   5
2   3
3   1
3   Null
3   Null
4   2
5   8
5   Null
5   Null

I'm trying to avoid using a cursor but I can't figure out how to get this working without one.

Upvotes: 3

Views: 141

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

You can do this with a correlated subquery. Here is one way:

select (select count(box) from t t2 where t2.row <= t.row) as row,
       box
from t
order by row;

This is counting the number of valid box values up to a given row.

In SQL Server 2012, you can do this with a cumulative count():

select count(box) over (order by row) as row, box
from t
order by row;

These assume that row is set as in the question. If row does not start with those values, then you have a problem. SQL tables are inherently unordered, and you need some column to specify the ordering.

Upvotes: 3

Related Questions