SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (SiteName VARCHAR(20), SampleNumber INT);

insert into mytable values('site1', 1), ('site1', 2), ('site1', 3), ('site1', 5), ('site1', 6), ('site1', 8), ('site1', 9), ('site1', 10);
8 rows affected

select * from mytable;


SiteName
SampleNumber


site1
1

site1
2

site1
3

site1
5

site1
6

site1
8

site1
9

site1
10


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)


SiteName
SampleRange


site1
1-3

site1
5-6

site1
8-10


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


SiteName
SampleRange


site1
1-3,5-6,8-10