3번째 자리까지 동일한 ip 로 그룹핑 하면 됩니다.
다만, 4번째 자리가 연속인 것끼리 그룹핑 하는지?
연속 상관없이 3번째 자리만 동일하면 그룹핑 하는 것인지?
예시 자료만으로는 판단이 안되네요?

group by ip 하면 되지 않을까요?

WITH T AS (
select 1 as seq, '192.168.1.2' as ip union
select 2 as seq, '192.168.1.3' as ip union
select 3 as seq, '192.168.1.4' as ip union
select 4 as seq, '192.168.1.5' as ip union
select 5 as seq, '192.168.1.6' as ip union
select 6 as seq, '192.168.5.5' as ip union
select 7 as seq, '192.168.7.8' as ip union
select 8 as seq, '192.168.7.9' as ip union
select 9 as seq, '192.168.10.2' as ip union
select 10 as seq, '192.168.10.3' as ip union
select 11 as seq, '192.168.10.4' as ip union
select 12 as seq, '192.168.10.5' as ip union
select 12 as seq, '192.168.10.15' as ip
) , T2 AS ( 
SELECT A.*
    , SEQ - ROW_NUMBER() OVER(PARTITION BY GROUP3 ORDER BY GROUP4) GRP 
    FROM ( SELECT T.*
            , LEFT(SUBSTRING( IP,CHARINDEX('.',IP,8) + 1 , LEN(IP)), CHARINDEX('.',SUBSTRING( IP,CHARINDEX('.',IP,8) + 1 , LEN(IP)))-1) AS GROUP3
            , RIGHT(IP, CHARINDEX('.', REVERSE(IP)) - 1) AS GROUP4
         FROM T
     ) A 
)

SELECT STUFF((
          SELECT ',' + CAST(B.SEQ AS VARCHAR)
          FROM T2 B
          WHERE A.GRP = B.GRP
          FOR XML PATH('')
          ),1,1,'') AS SEQ
      ,MIN(A.IP) +  CASE WHEN MIN(A.IP) = MAX(A.IP) THEN '' ELSE '~' + MAX(A.IP) END  AS IP
  FROM T2 A
 GROUP BY GRP          

WITH t AS
(
SELECT 1 seq, '192.168.1.2' ip 
UNION ALL SELECT  2, '192.168.1.3'
UNION ALL SELECT  3, '192.168.1.4'
UNION ALL SELECT  4, '192.168.1.5'
UNION ALL SELECT  5, '192.168.1.6'
UNION ALL SELECT  6, '192.168.5.5'
UNION ALL SELECT  7, '192.168.7.8'
UNION ALL SELECT  8, '192.168.7.9'
UNION ALL SELECT  9, '192.168.10.2'
UNION ALL SELECT 10, '192.168.10.3'
UNION ALL SELECT 11, '192.168.10.4'
UNION ALL SELECT 12, '192.168.10.5'
UNION ALL SELECT 13, '192.168.10.7'
UNION ALL SELECT 14, '192.168.7.10'
)
, tmp AS
(
SELECT seq, ip
     , v1, v2, v3, v4
     , v4 - ROW_NUMBER() OVER(PARTITION BY v1, v2, v3 ORDER BY v4) grp
  FROM (SELECT seq, ip
             , SUBSTRING(ip, p0+1, p1-p0-1)+0 v1
             , SUBSTRING(ip, p1+1, p2-p1-1)+0 v2
             , SUBSTRING(ip, p2+1, p3-p2-1)+0 v3
             , SUBSTRING(ip, p3+1, p4-p3-1)+0 v4
          FROM (SELECT seq, ip
                     , 0 p0
                     , CHARINDEX('.', ip, 1) p1
                     , CHARINDEX('.', ip
                     , CHARINDEX('.', ip, 1) + 1) p2
                     , CHARINDEX('.', ip
                     , CHARINDEX('.', ip
                     , CHARINDEX('.', ip, 1) + 1) + 1) p3
                     , LEN(ip) + 1 p4
                  FROM t
                ) a
        ) a
)
SELECT STUFF((SELECT CONCAT(',', seq)
                FROM tmp
               WHERE v1 = a.v1
                 AND v2 = a.v2
                 AND v3 = a.v3
                 AND grp = a.grp
               ORDER BY v4
                 FOR XML PATH('')
              ), 1, 1, '') seq
     , CONCAT( v1, '.', v2, '.', v3, '.', MIN(v4)
             , CASE WHEN COUNT(*) = 1 THEN ''
                    ELSE CONCAT(' ~ ', v1, '.', v2, '.', v3, '.', MAX(v4))
                END
             ) ip
  FROM tmp a
 GROUP BY v1, v2, v3, grp
;

두분 답변 너무 감사드립니다!

이렇게 보니 원리 자체는 어렵지 않은거였는데 지레 겁먹고는 생각을 너무 좁게 하고 있었네요

더 많은 훈련을 해야겠습니다..