CODEWITHSUNDEEP
sqlsql-server
Aborigen
Aborigen

Reputation: 21

Grouping data to create range columns

I have the following table:

Row   Column   Type
1     1        =
1     2        =
1     3        O
1     4        =
1     5        =
1     6        O
2     1        =

And I need to get something like that

Row   Start_Column   End_Column   Type
1     1              2           =
1     3              3           O
1     4              5           =
1     6              6           O
2     1              1           =

I tried to group it, to manipulate with ROW_NUMBER, RANK but with no luck

Does anyone have any idea how to do this?

Upvotes: 1

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is a type of gaps-and-islands problem. In this case, the simplest method is probably the difference of row numbers:

select row, type, min(column), max(column)
from (select t.*,
             row_number() over (partition by row, type order by column) as seqnum_2,
             row_number() over (partition by row order by column) as seqnum
      from t
     ) t
group by row, type, (seqnum - seqnum_2)
order by row, min(column);

If column is sequential with no gaps, you can simplify this even more:

select row, type, min(column), max(column)
from (select t.*,
             row_number() over (partition by row, type order by column) as seqnum_2
      from t
     ) t
group by row, type, (column - seqnum_2)
order by row, min(column);

Why does this work? Well, if you subtract an increasing sequence from column, then the result is constant -- when the types are the same.

Here is a db<>fiddle.

Upvotes: 2

forpas
forpas

Reputation: 164099

You can use LAG() and SUM() window functions to create the groups that you want and then aggregate:

SELECT [Row], 
       MIN([Column]) Start_Column, 
       MAX([Column]) End_Column, 
       MAX([Type]) [Type]
FROM (
  SELECT *, SUM(flag) OVER (PARTITION BY [Row] ORDER BY [Column]) grp
  FROM (
    SELECT *, 
      CASE WHEN [Type] = LAG([Type]) OVER (PARTITION BY [Row] ORDER BY [Column]) THEN 0 ELSE 1 END flag
    FROM tablename
  ) t
) t
GROUP BY [Row], grp
ORDER BY [Row], grp

See the demo.
Results:

Row | Start_Column | End_Column | Type
--- | ------------ | ---------- | ----
  1 |            1 |          2 | =   
  1 |            3 |          3 | O   
  1 |            4 |          5 | =   
  1 |            6 |          6 | O   
  2 |            1 |          1 | =

Upvotes: 2

Related Questions