SQL Server 2016

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

CREATE TABLE [Table_1](
    [ID] [int] NULL,
    [UpStart] [int] NULL,
    [UpEnd] [int] NULL,
    [SStart] [int] NULL,
    [SEnd] [int] NULL,
    [DnStart] [int] NULL,
    [DnEnd] [int] NULL
) ON [PRIMARY]

INSERT INTO [Table_1]
       ([ID]        
       ,[UpStart]
       ,[UpEnd]
       ,[SStart]
       ,[SEnd]
       ,[DnStart]
       ,[DnEnd])
   VALUES
       (1,98,99,100,104,105,106),
       (2,98,99,100,104,105,106),
       (3,100,101,102,106,107,108),
       (4,100,101,102,106,107,108)
       
select * from table_1;


ID
UpStart
UpEnd
SStart
SEnd
DnStart
DnEnd


1
98
99
100
104
105
106

2
98
99
100
104
105
106

3
100
101
102
106
107
108

4
100
101
102
106
107
108


CREATE TABLE [Table_2](
  [Position] [int] NULL,
  [Seq] [nvarchar](1) NULL -- changed from nchar(10)
 ) ON [PRIMARY]

INSERT INTO [dbo].[Table_2]
       ([Position]
       ,[Seq])
 VALUES
       (98,'M'),
       (99,'N'),
       (100,'A'),
       (101,'T'),
       (102,'C'),
       (103,'T'),
       (104,'G'),
       (105,'T'),
       (106,'T'),
       (107,'G'),
       (108,'T'),
       (109,'G')
       
select * from table_2;


Position
Seq


98
M

99
N

100
A

101
T

102
C

103
T

104
G

105
T

106
T

107
G

108
T

109
G


with cte_Up as
(
  select t1.Id,
         ( select '' + t2.Seq
           from table_2 t2
           where t2.Position >= t1.UpStart
             and t2.Position <= t1.UpEnd
           order by t2.Position
           for xml path('') ) as UpSeq
  from table_1 t1
),
cte_S as
(
  select t1.Id,
         ( select '' + t2.Seq
           from table_2 t2
           where t2.Position >= t1.SStart
             and t2.Position <= t1.SEnd
           order by t2.Position
           for xml path('') ) as SSeq
  from table_1 t1
),
cte_Dn as
(
  select t1.Id,
         ( select '' + t2.Seq
           from table_2 t2
           where t2.Position >= t1.DnStart
             and t2.Position <= t1.DnEnd
           order by t2.Position
           for xml path('') ) as DnSeq
  from table_1 t1
)
select t1.Id,
       t1.UpStart,
       t1.UpEnd,
       u.UpSeq,
       t1.SStart,
       t1.SEnd,
       s.SSeq,
       t1.DnStart,
       t1.DnEnd,
       d.DnSeq
from table_1 t1
join cte_Up u
  on u.Id = t1.Id
join cte_S s
  on s.Id = t1.Id
join cte_Dn d
  on d.Id = t1.Id;


Id
UpStart
UpEnd
UpSeq
SStart
SEnd
SSeq
DnStart
DnEnd
DnSeq


1
98
99
MN
100
104
ATCTG
105
106
TT

2
98
99
MN
100
104
ATCTG
105
106
TT

3
100
101
AT
102
106
CTGTT
107
108
GT

4
100
101
AT
102
106
CTGTT
107
108
GT

ID	UpStart	UpEnd	SStart	SEnd	DnStart	DnEnd
1	98	99	100	104	105	106
2	98	99	100	104	105	106
3	100	101	102	106	107	108
4	100	101	102	106	107	108

Id	UpStart	UpEnd	UpSeq	SStart	SEnd	SSeq	DnStart	DnEnd	DnSeq
1	98	99	MN	100	104	ATCTG	105	106	TT
2	98	99	MN	100	104	ATCTG	105	106	TT
3	100	101	AT	102	106	CTGTT	107	108	GT
4	100	101	AT	102	106	CTGTT	107	108	GT

ID	UpStart	UpEnd	SStart	SEnd	DnStart	DnEnd
1	98	99	100	104	105	106
2	98	99	100	104	105	106
3	100	101	102	106	107	108
4	100	101	102	106	107	108

ID	UpStart	UpEnd	SStart	SEnd	DnStart	DnEnd
1	98	99	100	104	105	106
2	98	99	100	104	105	106
3	100	101	102	106	107	108
4	100	101	102	106	107	108