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
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:And deducting the row number from the pin address will give you a constant value for each continuous range:
The query thus far is simply:
Then you can group by your constant value and ID, and use
MIN
andMAX
to get the start and end of each range:Which, for ID 3 gives:
Finally, you need to concatenate the
RangeText
values into a single row, which can be done using SQL Server's XML Extensions.Which gives: