如何基于另一个表的多个列中的值从表中提取多个行,然后在SQL中进行串联? [英] How to extract multiple rows from a table based on values from multiple columns from another table and then concatenate in SQL?
本文介绍了如何基于另一个表的多个列中的值从表中提取多个行,然后在SQL中进行串联?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
表1
开始 | 结束 |
---|---|
100 | 104 |
105 | 109 |
表2
位置 | Seq |
---|---|
100 | A |
101 | T |
102 | C |
103 | T |
104 | G |
105 | T |
106 | T |
107 | G |
108 | T |
109 | G |
我的最终结果必须是
开始 | 结束 | 序列 |
---|---|---|
100 | 104 | ATCTG |
105 | 109 | TTGTG |
我尝试使用以下语句将表2中的值连接起来
SELECT Sequence =(选择''+ Seq从表2其中位置> = 100,位置< = 104按位置排序FOR XML PATH('')
)
解决方案
您没有说明要使用的DBMS,因此这里是使用CTE和FOR XML进行转置的SQL Server解决方案:
;使用SequenceCTE AS(选择[开始],[结尾],序号从表1a加入表2 b开启b.位置> = a.[开始]并且b.位置< = a.[结束])选择地区a.[开始],a.[结束],(SELECT STUFF(','+ Seq,1,1,'')FROM SequenceCTE b其中a.[开始] = b.[开始]并且a.[结束] = b.[结束]FOR XML路径(''))FROM SequenceCTE a
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('')
)
解决方案
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
这篇关于如何基于另一个表的多个列中的值从表中提取多个行,然后在SQL中进行串联?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文