我需要转换查询
select
unnest(string_to_array(names,',')) as "Admin Name",unnest(string_to_array(phones,')) as "Admin Phone",unnest(string_to_array(emails,')) as "Admin Emails"
from
metadata_Table
等效的SQL Server查询。
有什么建议吗？
将PostgreSQL查询转换为SQL Server查询
•
问答
gaoyueze 回答：将PostgreSQL查询转换为SQL Server查询
在SL Server中，您不能轻易做到这一点。没有“具有普通性的string_split（）”。而且，string_split()不能保证排序。
在Microsoft增强此功能之前，我的建议是递归子查询：
with cte as (
select convert(varchar(max),null) as name,convert(varchar(max),null) as phone,null) as email,names + ',') as names_rest,phones + ',') as phones_rest,emails + ',') as emails_rest,0 as lev
from metadata_Table
union all
select left(names_rest,charindex(',',names_rest) - 1),left(phones_rest,phones_rest) - 1),left(emails_rest,emails_rest) - 1),stuff(names_rest,1,names_rest),''),stuff(phones_rest,phones_rest),stuff(emails_rest,emails_rest),lev + 1
from cte
where emails_rest like '%,%'
)
select *
from cte
where lev > 0;