Here is my dataset, where order is the fix sequence of each product.
What I want is another column in here, lets say TagID
. TagID
is an int
value that will based on the aggregate Count column, group it by product if it is greater or equal to 5.
So the dataset would look like this:
How can I accomplish this in SQL Server?
Thanks in advance.
Advertisement
Answer
This requires a recursive CTE. You already have a numbering column, so this is pretty simple:
with cte as ( select product, order, count, 1 as grp, count as s from t where order = 1 union all select cte.product. t.order, t.count, (case when cte.s + t.count >= 5 then grp + 1 else grp end), (case when cte.s + t.count >= 5 then t.count else cte.s + t.count end) from cte join t on t.product = cte.product and t.order = cte.order + 1 ) select * from cte;