SQL Server  Split rows in a specific column
I would like to split the rows of a column given a separator (in my specific case, it would be 0), treating the column like a traditional linked list (e.g. a Python list). Then, obtain the mode of each cluster and replace each value of the cluster with his mode.
Let's say I have the following table:
 ID  Data
++
 1  0
 2  0
 3  0
 4  1
 5  2
 6  2
 7  0
 8  0
 9  1
 10  2
 11  1
 12  0
The associated Python's list would be:
Data = [0, 0, 0, 1, 2, 2, 0, 0, 1, 2, 1, 0]
The clusters would be the following:
Cluster[0] = [1, 2, 2]
Cluster[1] = [1, 2, 1]
The desired output as a table would be:
 ID  Data
++
 1  0
 2  0
 3  0
 4  2
 5  2
 6  2
 7  0
 8  0
 9  1
 10  1
 11  1
 12  0
2 answers

You can do this by counting the number of zeros before each value and then using window functions to identify the mode:
select t.id, t.data, (case when data = 0 then 0 else first_value(data) over (partition by grp order by cnt desc) end) as mode from (select t.id, t.data, t.grp, (case when data = 0 then 0 else count(*) over (partition by grp, data) end) as cnt from (select t.*, sum(case when data = 0 then 1 else 0 end) over (order by id) as grp from t ) t ) t
Here is a SQL Fiddle.
I don't like all the
case when data = 0
logic, but it appears necessary given the way that the groups are defined. 
Here's one approach
Example
Declare @YourTable Table ([ID] int,[Data] int) Insert Into @YourTable Values (1,0) ,(2,0) ,(3,0) ,(4,1) ,(5,2) ,(6,2) ,(7,0) ,(8,0) ,(9,1) ,(10,2) ,(11,1) ,(12,0) ;with cte as ( Select ID ,Data ,Grp = sum(Flg) over (Order by ID) * sign(Data) From ( Select * ,Flg = case when lag(Data,1,Data) over (Order by ID) = 0 and Data<>0 then 1 else 0 end From @YourTable ) A ) Select A.ID ,Data = isnull(B.Data,0) From cte A Left Join (Select Top 1 with Ties Grp,Data,hits=count(*) From cte Where Data<>0 Group By Grp,Data Order By Row_Number() over (partition by Grp order by count(*) desc) ) B on A.Grp=B.Grp
Returns
ID Data 1 0 2 0 3 0 4 2 5 2 6 2 7 0 8 0 9 1 10 1 11 1 12 0