Get a range of numbers from a table in MSSQL
I have a table in MSSQL 2008R2:
ID | PinAddress ------------------------------------- 1 | 1 1 | 2 1 | 3 1 | 4 1 | five 1 | 6 1 | sixteen 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, when I search for ID = 1, I want a result like
1-6.16.31
When I search for ID = 2, I want to get a result like
55-57.81-84
When I search for ID = 3, I want a 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)
thank
source to share
These are gaps and island problems , and the key is defining your contiguous ranges that are done with 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 subtracting the line number from the address will give you a constant value for each contiguous 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
So far, the request is simple:
SELECT ID,
PinAddress,
GroupingSet = PinAddress - ROW_NUMBER() OVER(PARTITION BY ID ORDER BY PinAddress)
FROM dbo.PinAddress;
Then you can group 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;
What for ID 3 gives:
ID RangeStart RangeEnd RangeText
-----------------------------------------
3 101 103 101 - 103
3 107 109 107 - 109
Finally, you need to concatenate the values RangeText
into a single string, which can be done with SQL Server 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;
What gives:
ID Ranges
------------------------------
1 1 - 6, 16 - 16, 31 - 31
2 55 - 57, 81 - 84
3 101 - 103, 107 - 109
source to share
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
source to share
@GarethD Your logic with ROW_NUMBER was great and works like a charm. I took advantage of your request and modified it a bit to get the desired result:
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;
source to share
The main version. If you want to get a result set for the entire ID 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
source to share