# Sequential group in SQL

 0 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 11 ● 1 ● 1 ● 1 Melvyn Harbour 1 ♦♦ 1.4k ● 18 ● 20 ● 22 Don't you mean 1-3,6-8,9-14,15,16? Oct 21 '09 at 11:40 AM Bob Hovious add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 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 `` more ▼ answered Oct 21 '09 at 01:51 PM Tom Staab 5.8k ● 6 ● 8 ● 10 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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 5.7k ● 15 ● 18 ● 20 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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 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 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

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

By Email:

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

Topics:

x1933

asked: Oct 21 '09 at 11:22 AM

Seen: 942 times

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