CODEWITHSUNDEEP
sqlsql-server
niydiyin
niydiyin

Reputation: 67

Row_number() that depends on condition

I have one column with zeros and ones with preserved order, where zeros indicate breaks between pieces of data. Here is the example

A Ord
1 1 
1 2 
0 3 
0 4 
0 5 
1 6 
1 7 
1 8 
0 9 
1 10

What I would like to obtain is the same column where all elements after zero (or zeros) increase by 1 in comparison to previous pack of non-zero elements. Thus above example after transformartion has to look like this:

A Ord
1 1
1 2
0 3
0 4
0 5
2 6
2 7
2 8
0 9
3 10

The number of zeros and ones in each group can be any. I tried to do it using different combinations of row_number() and join, but eventually failed.

Upvotes: 3

Views: 135

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

If you are on SQL Server 2012 you can use lag() and sum() over().

select iif(T.A = 0, 0, sum(T.C) over(order by T.Ord rows unbounded preceding)) as A,
       T.Ord
from (
     select Y.A,
            Y.Ord,
            iif(Y.A = 1 and isnull(lag(Y.A) over(order by Y.Ord), 0) = 0, 1, 0) as C
     from YourTable as Y
     ) as T

SQL Fiddle

Upvotes: 4

Related Questions