SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t
(
    [pID] varchar(7), 
    [vID] int, 
    [vdStart] int, 
    [vdEnd] int
);
    
INSERT INTO t ([pID], [vID], [vdStart], [vdEnd])
VALUES
    ('Jenkins', 1, 100, 102),
    ('Jenkins', 3, 102, 110),
    ('Jenkins', 7, 111, 130),
    ('Barnaby', 2, 90, 114),
    ('Barnaby', 5, 114, 140),
    ('Barnaby', 9, 153, 158),
    ('Forster', 4, 100, 130),
    ('Smith', 6, 120, 131),
    ('Smith', 8, 140, 160),
    ('Everett', 10, 158, 165),
    ('Everett', 12, 165, 175),
    ('Everett', 15, 186, 190),
    ('Everett', 17, 190, 195),
    ('Everett', 18, 195, 199),
    ('Everett', 19, 199, 210)
;
15 rows affected

WITH cte AS (
    SELECT pID, vID, vdStart, vdEnd, GroupID = vdStart
    FROM t
    WHERE NOT EXISTS (SELECT 1
        FROM t Other
        WHERE Other.pID = t.pID
          AND t.vdStart BETWEEN Other.vdEnd AND Other.vdEnd + 1)
    
    UNION ALL
    
    SELECT t.pID, t.vID, t.vdStart, t.vdEnd, cte.GroupID
    FROM cte
    JOIN t ON t.pID = cte.pID AND t.vdStart BETWEEN cte.vdEnd AND cte.vdEnd + 1
),
Numbered AS (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY cte.pID, cte.GroupID ORDER BY cte.vdStart)
    FROM cte
)
SELECT
  n.pID, 
  vdStart = MIN(n.vdStart),
  vdEnd = MIN(n.vdEnd),
  vID  = MIN(CASE WHEN n.rn = 1 THEN n.vID END),
  vID1 = MIN(CASE WHEN n.rn = 2 THEN n.vID END),
  vID2 = MIN(CASE WHEN n.rn = 3 THEN n.vID END),
  vID3 = MIN(CASE WHEN n.rn = 4 THEN n.vID END)
FROM Numbered n
GROUP BY
  n.pID,
  n.GroupID
ORDER BY
  n.pID,
  n.GroupID;


pID
vdStart
vdEnd
vID
vID1
vID2
vID3


Barnaby
90
114
2
5
null
null

Barnaby
153
158
9
null
null
null

Everett
158
165
10
12
null
null

Everett
186
190
15
17
18
19

Forster
100
130
4
null
null
null

Jenkins
100
102
1
3
7
null

Smith
120
131
6
null
null
null

Smith
140
160
8
null
null
null


Warning: Null value is eliminated by an aggregate or other SET operation.


WITH cte1 AS (
    SELECT *,
      PrevEnd = LAG(t.vdEnd) OVER (PARTITION BY t.pID ORDER BY t.vdStart)
    FROM t
),
cte2 AS (
    SELECT *,
      GroupId = COUNT(CASE WHEN cte1.PrevEnd >= cte1.vdStart - 1 THEN NULL ELSE 1 END)
            OVER (PARTITION BY cte1.pID ORDER BY cte1.vdStart ROWS UNBOUNDED PRECEDING)
    FROM cte1
),
Numbered AS (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY cte2.pID, cte2.GroupID ORDER BY cte2.vdStart)
    FROM cte2
)
SELECT
  n.pID, 
  vdStart = MIN(n.vdStart),
  vdEnd = MIN(n.vdEnd),
  vID  = MIN(CASE WHEN n.rn = 1 THEN n.vID END),
  vID1 = MIN(CASE WHEN n.rn = 2 THEN n.vID END),
  vID2 = MIN(CASE WHEN n.rn = 3 THEN n.vID END),
  vID3 = MIN(CASE WHEN n.rn = 4 THEN n.vID END)
FROM Numbered n
GROUP BY
  n.pID,
  n.GroupID
ORDER BY
  n.pID,
  n.GroupID;


pID
vdStart
vdEnd
vID
vID1
vID2
vID3


Barnaby
90
114
2
5
null
null

Barnaby
153
158
9
null
null
null

Everett
158
165
10
12
null
null

Everett
186
190
15
17
18
19

Forster
100
130
4
null
null
null

Jenkins
100
102
1
3
7
null

Smith
120
131
6
null
null
null

Smith
140
160
8
null
null
null


Warning: Null value is eliminated by an aggregate or other SET operation.

pID	vdStart	vdEnd	vID	vID1	vID2	vID3
Barnaby	90	114	2	5	null	null
Barnaby	153	158	9	null	null	null
Everett	158	165	10	12	null	null
Everett	186	190	15	17	18	19
Forster	100	130	4	null	null	null
Jenkins	100	102	1	3	7	null
Smith	120	131	6	null	null	null
Smith	140	160	8	null	null	null