Get Numbers Range from Table in MSSQL

Question

I have a table in MSSQL 2008R2:

    ID  |  PinAddress
-------------------------------------
   1  |   1
   1  |   2
   1  |   3
   1  |   4
   1  |   5
   1  |   6
   1  |   16
   1  |   31
   2  |   55
   2  |   56
   2  |   57
   2  |   81
   2  |   82
   2  |   83
   2  |   84
   3  |   101
   3  |   102
   3  |   103
   3  |   107
   3  |   108
   3  |   109

What I want is when I search for ID = 1,I want result like

1-6,16,31

When I search for ID = 2,I want result like

55-57,81-84

When I search for ID = 3,I want result like

101-103,107-109

You can use below script to create table and data:

CREATE TABLE PinAddress(ID INT,PinAddress INT)
INSERT INTO PinAddress values(1,1)
INSERT INTO PinAddress values(1,2)
INSERT INTO PinAddress values(1,3)
INSERT INTO PinAddress values(1,4)
INSERT INTO PinAddress values(1,5)
INSERT INTO PinAddress values(1,6)
INSERT INTO PinAddress values(1,16)
INSERT INTO PinAddress values(1,31)
INSERT INTO PinAddress values(2,55)
INSERT INTO PinAddress values(2,56)
INSERT INTO PinAddress values(2,57)
INSERT INTO PinAddress values(2,81)
INSERT INTO PinAddress values(2,82)
INSERT INTO PinAddress values(2,83)
INSERT INTO PinAddress values(2,84)
INSERT INTO PinAddress values(3,101)
INSERT INTO PinAddress values(3,102)
INSERT INTO PinAddress values(3,103)
INSERT INTO PinAddress values(3,107)
INSERT INTO PinAddress values(3,108)
INSERT INTO PinAddress values(3,109)

Thanks

1-6,16,31? What is the logic behind those partitions? I can not figure out — Giorgi Nakeuri, Jun 12 '15 at 11:48
can you explain the logic for selecting the data ?? which criteria you are used ??? — Arunprasanth K V, Jun 12 '15 at 11:51
it may be my problem but i did not get your logic i mean how 1-6,16,31 ,55-57,81-84,101-103,107-109 are came — Arunprasanth K V, Jun 12 '15 at 12:11

score 7 · Accepted Answer · edited May 23 '17 at 12:32

This is a gaps and islands problem, and the key is identifying your continuous ranges, which is done using ROW_NUMBER(). So for ID 3, you have:

ID  PinAddress  RowNumber
---------------------------
3   101         1
3   102         2
3   103         3
3   107         4
3   108         5
3   109         6

And deducting the row number from the pin address will give you a constant value for each continuous range:

ID  PinAddress  RowNumber   (PinAddress - RowNumber)
---------------------------------------------------
3   101         1           100
3   102         2           100
3   103         3           100
---------------------------------------------------
3   107         4           103
3   108         5           103
3   109         6           103

The query thus far is simply:

SELECT  ID,
        PinAddress,
        GroupingSet = PinAddress - ROW_NUMBER() OVER(PARTITION BY ID ORDER BY PinAddress)
FROM    dbo.PinAddress;

Then you can group by your constant value and ID, and use MIN and MAX to get the start and end of each range:

WITH RankedData AS
(   SELECT  ID,
            PinAddress,
            GroupingSet = PinAddress - ROW_NUMBER() OVER(PARTITION BY ID ORDER BY PinAddress)
    FROM    PinAddress
)
SELECT  ID,
        RangeStart = MIN(PinAddress),
        RangeEnd = MAX(PinAddress),
        RangeText = CONVERT(VARCHAR(10), MIN(PinAddress)) + 
                                    CASE WHEN MIN(PinAddress) = MAX(PinAddress) THEN '' 
                                        ELSE ' - ' + CONVERT(VARCHAR(10), MAX(PinAddress))
                                    END
FROM    RankedData
GROUP BY ID, GroupingSet;

Which, for ID 3 gives:

ID  RangeStart  RangeEnd    RangeText
-----------------------------------------
3   101         103         101 - 103
3   107         109         107 - 109

Finally, you need to concatenate the RangeText values into a single row, which can be done using SQL Server's XML Extensions.

WITH RankedData AS
(   SELECT  ID,
            PinAddress,
            GroupingSet = PinAddress - ROW_NUMBER() OVER(PARTITION BY ID ORDER BY PinAddress)
    FROM    PinAddress
)
SELECT  p.ID,   
        Ranges = STUFF((SELECT  ', ' + CONVERT(VARCHAR(10), MIN(PinAddress)) + 
                                    CASE WHEN MIN(PinAddress) = MAX(PinAddress) THEN '' 
                                        ELSE ' - ' + CONVERT(VARCHAR(10), MAX(PinAddress))
                                    END
                        FROM    RankedData AS rd
                        WHERE   rd.ID = p.ID
                        GROUP BY ID, GroupingSet
                        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')
FROM    (SELECT DISTINCT ID FROM PinAddress) AS p;

Which gives:

ID      Ranges
------------------------------
1       1 - 6, 16 - 16, 31 - 31
2       55 - 57, 81 - 84
3       101 - 103, 107 - 109

sbiz · Answer 2 · 2015-06-12T12:04:14.073

Try this code

DECLARE @values VARCHAR(8000) 
DECLARE @prevseq int
SET @values = ''



SELECT @values = @values + 
    (CASE WHEN @values = '' OR @values like '%,' THEN cast(PinAddress as varchar) --first value or new after sequence
          WHEN PinAddress - 1 = @prevseq THEN '' 
          ELSE '-' + cast (@prevseq as varchar) + ',' + cast(PinAddress as varchar) 
     END),
        @prevseq = coalesce(PinAddress, -1)
FROM PinAddress 
WHERE ID = 1
ORDER BY PinAddress ASC

SELECT @values = @values + 
        (CASE WHEN @values not like '%' + cast(@prevseq as varchar) THEN '-' + cast(@prevseq as varchar) ELSE '' END)

PRINT @values

score 0 · Answer 3 · answered Jun 12 '15 at 12:39

@GarethD your logic with ROW_NUMBER was excellent and works like a charm. I took help of your query and changed it little bit to get my desired output:

WITH RankedData AS
(
    SELECT ID,
           PinAddress,
           GroupingSet = PinAddress - ROW_NUMBER() OVER(PARTITION BY ID ORDER BY PinAddress)           
    FROM   dbo.PinAddress
    WHERE  ID = 1
)
SELECT p.ID,
       Ranges = STUFF(
           (            
               SELECT CASE WHEN MIN(pinaddress) = MAX(PINADDRESS) THEN
                ', ' + CONVERT(VARCHAR(10), MIN(PinAddress))
                ELSE 
                 ', ' + CONVERT(VARCHAR(10), MIN(PinAddress)) + '-' + 
                      CONVERT(VARCHAR(10), MAX(PinAddress))
                    END  
               FROM   RankedData AS rd
               WHERE  rd.ID = p.ID
               GROUP BY
                      ID,
                      GroupingSet
                      FOR XML PATH(''),
                      TYPE
           ).value('.', 'VARCHAR(MAX)'),
           1,
           2,
           ''
       )
FROM   (
           SELECT DISTINCT ID
           FROM   RankedData
       ) AS p;

Azar · Answer 4 · 2015-06-12T11:54:36.317

-1

Basic Version, If you want to fetch the result set for all ID's then create a scalar function.

declare @id int = 1
declare @formed varchar(max)
Select @Formed = ISNULL(@formed+',','')+Formed
from
(
Select  
 Formed = convert(varchar,MIN([PinAddress]))
            +case when MIN([PinAddress]) != MAX([PinAddress]) then '-'
            +convert(varchar,MAX([PinAddress] )) else '' end
from PinAddress  where ID = @id            
group by [PinAddress]/case when [#PinAddress]/10= 0 then 10 else 5 end)t

select @formed

edited Jun 12 '15 at 11:54

answered Jun 12 '15 at 11:44

Azar

1,852
15
17

Azar your query almost does the trick. But when I check for ID = 3 it does not produce desired result. When I check for ID = 3 it generates output 101-109 which should be 101-103,107-109. Can you please check? – Anand Shah Jun 12 '15 at 11:50
Sorry I haven't noticed that can you try the SQL now. – Azar Jun 12 '15 at 11:54
your code does not work if you have the first sequence from 1 to 11 – sbiz Jun 12 '15 at 12:15

Get Numbers Range from Table in MSSQL

4 Answers4