data sequence handing

Question

I am stuck with a weird problem of handling data sequences. My source data looks like -

Roll-on, Marker
  1,1
  2,0
  3,0
  5,1
  8,1
  9,0
 10,1

the marker column can only have two values, 1 and 0

if the roll no column is in a sequence, the marker value of 1 indicates the start of the sequence and all the remaining roll no will have marker value 0 within that sequence. So for roll no sequence 1-3, marker value is 1 for roll no 1 and 0 for the rest. However, if roll no doesn't fall into a sequence(as in roll no 8), the marker value is 1. From this data I need to create an output as follows -

Roll range
 1
 2
 3
 1-3
 5
 5-5
 8
 9
10
 8-10

Meaning -

display the roll no in sequence as in the input
after each sequence ends, display a new record containing the start and end roll no of the proceeding sequence

How is this possible?

Thanks in advance for help.

The tag sql says: "...and a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used." Please correct the tags — Luuk
– Luuk, Commented May 15, 2022 at 8:13
What did you try yourself? Can you share how your tables are created? Can you explain 8-10 ? ( Because 8 was not "in a sequence" ) — Luuk
– Luuk, Commented May 15, 2022 at 8:15
Why did you want to get 8-10 instead of 8-9 and 10-10? because there are two Marker — D-Shih
– D-Shih, Commented May 15, 2022 at 8:22
It's an interesting problem but is the a real-world problem you are trying to solve? Or is it an exercise. — Peter Smith
– Peter Smith, Commented May 15, 2022 at 8:56
@ D Shih - it's a typo. you can consider roll 11 instead of 10 to break the sequence — Subhash Ghai
– Subhash Ghai, Commented May 18, 2022 at 2:39

D-Shih · Accepted Answer · 2022-05-15 08:42:23Z

It seems like an island and gap problem.

If I understand correctly, we can try to use SUM window function with conditions to make it.

Generator a gap of row number then getting min and max group by

SELECT CONCAT(MIN(Roll),'-',MAX(Roll))
FROM (
 SELECT *,
        SUM(CASE WHEN Marker = 1 THEN 1 ELSE 0 END) OVER(ORDER BY Roll) grp
 FROM T
) t1
GROUP BY grp

as I comment I am not sure about the logic of 8-10 (why isn't 8-9 and 10-10) from your expect result and columns description, I think we can try to judge Max of Roll then do some arithmetic.

SELECT CONCAT(MIN(Roll),'-',MAX(Roll))
FROM (
 SELECT *,
        SUM(CASE WHEN Marker = 1 THEN 1 ELSE 0 END) OVER(ORDER BY Roll) + IIF(MAX(Roll) OVER() = Roll, - Marker,0) grp
 FROM T
) t1
GROUP BY grp

so that the final query combines result set we can use UNION ALL

;WITH CTE AS (
 SELECT *,
        SUM(CASE WHEN Marker = 1 THEN 1 ELSE 0 END) OVER(ORDER BY Roll) + IIF(MAX(Roll) OVER() = Roll, - Marker,0) grp
 FROM T
)
SELECT [Roll range]
FROM (
  SELECT CONCAT(MIN(Roll),'-',MAX(Roll)) 'Roll range',MAX(Roll) seq
  FROM CTE t1
  GROUP BY grp
  UNION ALL
  SELECT CAST(Roll AS VARCHAR(5)),Roll
  FROM CTE t1
) t1
ORDER BY seq

sqlfiddle

Luuk · Accepted Answer · 2022-05-15 08:51:21Z

1

SELECT 
   CASE WHEN a=2 AND CHARINDEX('-',R)=0 THEN CONCAT(R,'-',R) ELSE R END as R, 
   R2, 
   a
FROM (
   SELECT 
      1 as a,
      CONVERT(VARCHAR(3), Roll) R, 
      Roll as R2
   FROM table1
   
   UNION ALL
   
   SELECT
      2,
      STRING_AGG(Roll,'-') R,
      MAX(Roll) as R2
   FROM (
      SELECT
         Roll,
         SUM(Marker) OVER (ORDER BY Roll) S
      FROM
         table1
         ) x 
   GROUP BY S
) x

ORDER BY R2,a

output:

R	R2	a
1	1	1
2	2	1
3	3	1
1-2-3	3	2
5	5	1
5-5	5	2
8	8	1
9	9	1
8-9	9	2
10	10	1
10-10	10	2

Columns R2 and a are added for correct sorting.
I group 8-9 and 10-10, but this question is still open, see comment

answered May 15, 2022 at 8:51

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Collectives™ on Stack Overflow

data sequence handing

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related