更新时间:2023-12-04 10:43:58
首先,必须将数据插入到某些临时表中. 这里是几种方式.然后运行以下查询:
First, you must insert your data to some temp table. Here are several ways. Then run this query:
with cte as (
select
company, replace(replace(replace(accounts,'(',''),')',''),'+','')+'#' accounts
from
(values ('company 1','#3000#3999'),('company 2','(#4000#4019)+(#4021#4024)')) data(company, accounts)
)
, rcte as (
select
company, stuff(accounts, ind1, ind2 - ind1, '') acc, substring(accounts, ind1 + 1, ind2 - ind1 - 1) accounts
from
cte
cross apply (select charindex('#', accounts) ind1) ca
cross apply (select charindex('#', accounts, ind1 + 1) ind2) cb
union all
select
company, stuff(acc, ind1, ind2 - ind1, ''), substring(acc, ind1 + 1, ind2 - ind1 - 1)
from
rcte
cross apply (select charindex('#', acc) ind1) ca
cross apply (select charindex('#', acc, ind1 + 1) ind2) cb
where
len(acc)>1
)
select company, accounts from rcte
order by company, accounts
option (maxrecursion 0)