# question

## Sequential group in SQL

I have a table as:

```ID  Nu
1   1,2,3,6,7,8,15,16
```

I need to show it as sequential group like:

```From    To
1-3 , 6-7 , 9-9 , 15-16
```
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
Don't you mean 1-3,6-8,9-14,15,16?
0 Likes 0 ·

·

Edit: This problem was too interesting to forget about. It took me awhile, but I combined dportas's solution with my function. The result is at the bottom of this answer.

Okay. I converted (a limited version of) the stored procedure to a function. If the values are delimited in a single column, use this to convert to a table and then use dportas's solution.

``````-- Usage:   	SELECT * FROM dbo.ConvertListToTable('1,2,3,4,5,6', ',')
-- =============================================
CREATE FUNCTION ConvertListToTable
(
@List varchar(max),
@Delimiter varchar(5)
)
RETURNS
@ListTable TABLE
(
value int -- use whatever data type makes sense for proper ordering
)
AS
BEGIN
DECLARE @ValueStartIndex int, @ValueEndIndex int

SET @ValueStartIndex = 1

WHILE @ValueStartIndex <= LEN(@List)
BEGIN
SET @ValueEndIndex = CHARINDEX(@Delimiter, @List, @ValueStartIndex)
IF @ValueEndIndex = 0
SET @ValueEndIndex = LEN(@List) + 1 -- get remainder of list

INSERT INTO @ListTable (value)
VALUES (SUBSTRING(@List, @ValueStartIndex, @ValueEndIndex - @ValueStartIndex))

SET @ValueStartIndex = @ValueEndIndex + 1
END
RETURN
END
GO
``````

Here's the sample data I used:

``````-- This creates a temporary Numbers table, but you might already have a permanent one. Just make sure the list goes high enough.
DECLARE @Numbers TABLE (num int)
INSERT INTO @Numbers
SELECT value
FROM dbo.ConvertListToTable('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20',',')

-- This would be your data table.
DECLARE @tbl TABLE (id int, list varchar(max))
INSERT INTO @tbl (id, list)
SELECT 1, '1,2,3,4,6,7,9,12'
UNION
SELECT 2, '1,3,5'
UNION
SELECT 3, '1,2,4,5,7,9'
``````

Once you have the function, here's how you call it. You'll need to change @tbl and @Numbers to work for your database.

``````;WITH t AS
(
SELECT id, num, num - ROW_NUMBER() OVER (ORDER BY id, num) r
FROM
(
SELECT t.id, n.num
FROM @tbl t, @Numbers n
WHERE EXISTS
(
SELECT LT.value
FROM dbo.ConvertListToTable(t.list, ',') LT
WHERE LT.value = n.num
)
) t1
)
SELECT id, MIN(num) mn, MAX(num) mx
FROM t
GROUP BY id, r
ORDER BY id, mn, mx
``````

Here's the result:

``````id  mn	mx
1   1	4
1   6	7
1   9	9
1   12	12
2   1	1
2   3	3
2   5	5
3   1	2
3   4	5
3   7	7
3   9	9
``````

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Assuming you have these numbers as rows in you table then try:

``````WITH t AS (
SELECT num, num - ROW_NUMBER() OVER (ORDER BY num) r
FROM tbl t1)
SELECT MIN(num) mn, MAX(num) mx
FROM t
GROUP BY r;
``````

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This is like the question at Stack Overflow.

Seems my answer is now used on their site, and presumably this one too!

http://stackoverflow.com/questions/1176011/sql-to-determine-minimum-sequential-days-of-access/1176255#1176255

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.