merge - How to extract multiple rows from a table based on values from multiple columns from another table and then concatenate in SQL?

Question

I have two tables, Table 1 and Table 2. Table 1 have columns "start" and "end" . Table 2 has column "position" and "Sequence". I would like to extract the sequences from Table 2 from position = start to position = end and the create a new column with the concatenated string.

Table 1

Start	End
100	104
105	109

question from:https://stackoverflow.com/questions/65837018/how-to-extract-multiple-rows-from-a-table-based-on-values-from-multiple-columns

Answer 1

You don't state what DBMS you are using so here is a SQL Server solution using a CTE and FOR XML to perform the transpose:

; WITH SequenceCTE AS
(
    SELECT  [Start],
            [End],
            Seq
    FROM    Table1 a
            JOIN Table2 b
                ON b.Position >= a.[Start] AND
                  b.Position <= a.[End]
)
SELECT  DISTINCT
        a.[Start],
        a.[End],
        (
            SELECT  STUFF(',' + Seq,1,1,'')
            FROM    SequenceCTE b
            WHERE   a.[Start] = b.[Start] AND
                    a.[End] = b.[end]
            FOR XML PATH ('') 
        )
FROM    SequenceCTE a