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 |
Table 2
| Position | Seq |
|---|---|
| 100 | A |
| 101 | T |
| 102 | C |
| 103 | T |
| 104 | G |
| 105 | T |
| 106 | T |
| 107 | G |
| 108 | T |
| 109 | G |
My final result needs to be
| Start | End | Sequence |
|---|---|---|
| 100 | 104 | ATCTG |
| 105 | 109 | TTGTG |
I tried concatenating the values in the Table 2 using the below statement
SELECT Sequence = (Select '' + Seq
from Table2
where Position >= 100 and Position <= 104
order by Position FOR XML PATH(''))
But I am not sure how to perform this in the entire table. Thank you very much for all the helpp
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments