Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

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

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

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


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

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...