jeudi 14 juillet 2016

How to partition records into groups defined by the total sum of a certain column?

For example, consider the following data:

DECLARE @data TABLE (Id INT, Value INT)
INSERT INTO @data VALUES
(0,1),
(1,2),
(2,3),
(3,1),
(4,1),
(5,1)

I want to transform this data into the following:

Id  Value  Group
0   1      0
1   2      0
2   3      1
3   1      2
4   1      2
5   1      2

When inspecting the items in the order of their Ids we compute the total running sum of Value. When it reaches 3 we say the group is complete and start a new group and a new total running sum. This way the given data produces 3 groups (when inspected in the specific order, of course).

Can I compute the Group in Sql Server 2012?

Aucun commentaire:

Enregistrer un commentaire