question

Khaled avatar image
Khaled asked

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
sql-server-2005
1 comment
10 |1200

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

Bob Hovious avatar image Bob Hovious commented ·
Don't you mean 1-3,6-8,9-14,15,16?
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered

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
10 |1200

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

David 1 avatar image
David 1 answered

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;
10 |1200

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

Rob Farley avatar image
Rob Farley answered

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

10 |1200

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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