Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

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?

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...