Columns Transformation to Sets boundaries

General
Enthusiast

Columns Transformation to Sets boundaries


i have a table with the following values for Sets of values, need to generate teh start and the end of each set

Values

~~~~~

1

2

3

4

5

11

12

13

14

15

16

17

i want to get a query perform transformation for the range of each consecutive set:

Desired output:

Range_Start      Range_End

~~~~~~~~     ~~~~~~~

1 5

11 17

Tags (1)
1 REPLY
Junior Contributor

Re: Columns Transformation to Sets boundaries

If the values are unique you can utilize that both your value column and a ROW_NUMBER are sequential:

SELECT
MIN(seqval) AS StartVal,
MAX(seqval) AS EndVal,
COUNT(*)
FROM
(
SELECT
-- assign the same grp as long as there's no gap
seqval - ROW_NUMBER() OVER (ORDER BY seqval ASC) AS grp,
seqval
FROM seqtest
) dt
GROUP BY grp
;

If values are non-unique you must switch to DENSE_RANK instead...