x

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

more ▼

asked Oct 21, 2009 at 11:22 AM in Default

avatar image

Khaled
11 1 1 3

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

Oct 21, 2009 at 11:40 AM Bob Hovious
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Oct 21, 2009 at 01:51 PM

avatar image

Tom Staab ♦
14.5k 7 14 19

(comments are locked)
10|1200 characters needed characters left

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;
more ▼

answered Oct 21, 2009 at 12:47 PM

avatar image

David 1
1.8k 3 5

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 22, 2009 at 06:59 AM

avatar image

Rob Farley
5.8k 16 22 28

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017

asked: Oct 21, 2009 at 11:22 AM

Seen: 1407 times

Last Updated: Oct 21, 2009 at 11:39 AM

Copyright 2016 Redgate Software. Privacy Policy