Making series with interval data of SQL rows

Question

Sorry if I used wrong words to explain my problem.

I have a table which keeps errors on a woven fabric while the Fabric Quality Control process. I have datas like this.

(in first roll there are an error which continues for 8 meters without any interruption. But in second roll even errors are same but has interruption they are not continuous errors)

how can I get such kind of result below?

Answer 1

That's known as the "Islands" problem. One solution is to use row_number() to form groups where the ErrorMeter changes in a different way from 1:

select  Fabric
,       Roll
,       min(ErrorMeter) as ErrorBeginMeter
,       max(ErrorMeter) as ErrorEndMeter
,       min(ErrorCode) as ErrorCode
from    (
        select  row_number() over (partition by Fabric, Roll 
                    order by ErrorMeter) - cast(ErrorMeter as int) as grp
        ,       *
        from    FabricErrors
        ) as SubQueryAlias
group by
        Fabric
,       Roll
,       grp

Live example at SQL Fiddle.

Answer 2

You'll want to use good ol' GROUP BY, with an aggregate MIN and MAX.

SELECT *,
    MIN(ErrorMeter) ErrorBeginMeter,
    MAX(ErrorMeter) ErrorEndMeter
FROM fabric_quality
GROUP BY Fabric,Roll

Edit: I just realized your result set is aggregating only rows with sequential error meters, this will require a more complicated solution.