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 '09 at 11:22 AM in Default

Khaled gravatar image

Khaled
11 1 1 1

Don't you mean 1-3,6-8,9-14,15,16?
Oct 21 '09 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 '09 at 01:51 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(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 '09 at 12:47 PM

David 1 gravatar image

David 1
1.8k 1 3

Great solution if the numbers are in rows. If they aren't, you could use my InsertListIntoTable sproc (sorry for self-promotion, but the code is an answer to another question: http://ask.sqlservercentral.com/questions/452/how-do-i-insert-or-update-an-array-of-values-without-iteration/510#510).

Hmm ... I think I'll write a table function version of the sproc.
Oct 21 '09 at 01:06 PM Tom Staab
(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 '09 at 06:59 AM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1933

asked: Oct 21 '09 at 11:22 AM

Seen: 938 times

Last Updated: Oct 21 '09 at 11:39 AM