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.
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
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句