SQL method to assign incremental group numbers for repeated values

Data and Databases
1

Issue Overview

I have a set of numbers that repeat, and I need to allocate group numbers to each set. For instance, when the numbers 1 to 5 show up repeatedly, the first group should be labeled as 1, the second as 2, and so on.

Example Input:

1
2
3
4
5
1
2
3
4
5

Expected Output:

Value | GroupNum
1       1
2       1
3       1
4       1
5       1
1       2
2       2
3       2
4       2
5       2

Application Scenario

In my case, I want to reformat normalized data into an easier-to-read layout. My data is structured like this:

ID | TypeCol | Info
1     1       01/15/2023
1     2       John
1     3       USA
1     4       1
1     1       02/15/2023
1     2       Mary
1     3       Canada
1     4       0

The TypeCol values mean:

  • 1 = Date
  • 2 = Name
  • 3 = Country
  • 4 = Availability (1, 0, or null)

Desired Format:

ID | Date       | Name  | Country | Availability
1    01/15/2023   John   USA       1
1    02/15/2023   Mary   Canada    0

I attempted to utilize ROW_NUMBER() but wasn’t able to implement the grouping correctly. How can I perform this conversion in SQL?

2

The trick is catching when your sequence restarts. Use LAG() to compare each TypeCol value with the previous one - when it’s smaller, that’s your break point. From there, you can create a running group counter with SUM() as a window function. Once you’ve got your groups sorted, just use CASE statements to pivot everything into columns. This approach works on most SQL databases and handles sequences of different lengths way better than trying to divide by fixed numbers.

3

try row_number() over (partition by (row_number() over (order by id) - 1) / 4 order by id) for grouping. it divides row positions by your type count (4), floors it to get group numbers, then you can pivot on typecol for your columns.

4

this looks like a pivot after grouping! try using lag() to catch when the sequence resets - like when TypeCol jumps from a higher number back to 1. what database are you using? that’ll change what approach works best.