Styjun

I have a list of sample ids for a site in the format of:
Sitename, Sample Number such that there is n number of sample numbers for a given site. For example, the data could be:

site1 | 1
site1 | 2

etc to an arbitrary n.

Using the following as a similar example, this data below would get the answer from the last select statement:

CREATE TABLE #SiteWithId(SiteId VARCHAR(50), SampleNumber INT)

INSERT INTO #SiteWithId
(
 SiteId,
 SampleNumber
)
values 
( 'test', -- SiteId - varchar(50)
 1 -- SampleNumber - int
 ),
 ('test',2),
 ('test',3),
 ('test',4),
 ('test',6),
 ('test',7)

 SELECT * FROM #SiteWithId
 DROP TABLE #SiteWithId
 --the answer
 SELECT 'test', '1-4,6-7'

Note, that the missing item creates a break in the final answer.

I know I can loop through the dataset in C# and create such an item. But does anyone know to create such a value using only sql so I can just spit out the needed values for the report? I think I could do a loop in sql too but I am scared it would be unscalable since that is not really what sql is made to do.

Is there a better way to do this other than a loop in sql or c#?

Here is a solution that relies on window functions. The difference between the SampleNumber of a record and its ROW_NUMBER() within groups of records having the same SiteName gives you the group it belongs to. Then, the outer query aggregates each group:

SELECT SiteName, CONCAT(MIN(SampleNumber), '-', MAX(SampleNumber)) SampleRange
FROM (
 SELECT 
 SiteName, 
 SampleNumber, 
 ROW_NUMBER() OVER(PARTITION BY SiteName ORDER BY SampleNumber) rn
 FROM mytable
) x
GROUP BY SiteName, (SampleNumber - rn)

Demo on DB Fiddle:

Sample data:

SiteName | SampleNumber
:------- | -----------:
site1 | 1
site1 | 2
site1 | 3
site1 | 5
site1 | 6
site1 | 8
site1 | 9
site1 | 10

Results:

SiteName | SampleRange
:------- | :----------
site1 | 1-3 
site1 | 5-6 
site1 | 8-10 

If you want all the ranges of each site concatenated in one record, you can add another level of aggregation and use STRING_AGG() (available since SQL Server 2017):

SELECT SiteName, STRING_AGG(SampleRange,',') SampleRange
FROM (
 SELECT SiteName, CONCAT(MIN(SampleNumber), '-', MAX(SampleNumber)) SampleRange
 FROM (
 SELECT 
 SiteName, 
 SampleNumber, 
 ROW_NUMBER() OVER(PARTITION BY SiteName ORDER BY SampleNumber) rn
 FROM mytable
 ) x
 GROUP BY SiteName, (SampleNumber - rn)
) y
GROUP BY SiteName

Demo:

SiteName | SampleRange 
:------- | :-----------
site1 | 1-3,5-6,8-10