sql - SQL Server - 通过从分隔符中识别来拆分多行的列值

Question

我有一个包含两列（ID、Email）和值的表，如下所示。

ID    |email
 1    |[e-mail];[e-mail];[e-mail]
 2    |[e-mail];[e-mail]

问题是我需要使用 sql server 检索如下这些值。

ID  |email
1    [e-mail]
1    [e-mail]
1    [e-mail]
2    [e-mail]
2    [e-mail]

标签： sqlsql-servertsql

Answer 1

对于 sql server 2016 或 > 2016

with cte as
(


 select 1 as id,   '[e-mail];[e-mail];[e-mail]' as email
 union all
 select 2    ,'[e-mail];[e-mail]'
) select id,value from cte CROSS APPLY STRING_SPLIT(email, ';');

id  value
1   [e-mail]
1   [e-mail]
1   [e-mail]
2   [e-mail]
2   [e-mail]