join - SQL - Create a new table by joining two tables based on Start and End values and then Concatenate rows based on Start and End values?

Question

This is a follow up question of How to extract multiple rows from a table based on values from multiple columns from another table and then concatenate in SQL?. I am new to SQL. I have 2 tables Table 1 and Table 2. Table 1 have columns ID, SSTART, SEND, UPSTART, UPEND, DNSTART, DNSTAND. Table 2 have columns Position and Seq.

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

question from:https://stackoverflow.com/questions/65895603/sql-create-a-new-table-by-joining-two-tables-based-on-start-and-end-values-and

Answer 1

I am using common table expressions (cte_Up, cte_S, cte_Dn) to limit the required groupings.

Solution 1

When working with SQL Server 2017 or later, you can use the STRING_AGG() function to concatenate columns.

with cte_Up as
(
  select t1.Id, string_agg(t2.Seq, '') within group (order by t2.Position) as UpSeq
  from table_1 t1
  join table_2 t2
    on  t2.Position >= t1.UpStart
    and t2.Position <= t1.UpEnd
  group by t1.Id
),
cte_S as
(
  select t1.Id, string_agg(t2.Seq, '') within group (order by t2.Position) as SSeq
  from table_1 t1
  join table_2 t2
    on  t2.Position >= t1.SStart
    and t2.Position <= t1.SEnd
  group by t1.Id
),
cte_Dn as
(
  select t1.Id, string_agg(t2.Seq, '') within group (order by t2.Position) as DnSeq
  from table_1 t1
  join table_2 t2
    on  t2.Position >= t1.DnStart
    and t2.Position <= t1.DnEnd
  group by t1.Id
)
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;

Fiddle to see things in action.

---

Solution 2

Using the for xml clause to achieve string concatenation when string_agg() is not availabe.

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;

Fiddle to see things in action.

---

Result

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