Create n new rows from raw data such as (1000....1000+n)

Question

I need to read data from an Excel workbook, where data is stored in this manner:

Company       Accounts
Company1      (#3000...#3999)
Company2      (#4000..4019)+(#4021..4024)

where the expected output, using a OLE DB Destination in SSIS would be:

Company       Accounts
Company1      3000
Company1      3001
Company1      3002
   .           .
   .           .
   .           .
Company1      3999
Company2      4000
Company2      4001
   .           .
   .           .
   .           .
Company2      4019
Company2      4021
   .           .
   .           .
Company2      4024

This has me perplexed, I don't know how to even begin process this problem.

Does someone have any insight into this?

Answer 1

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)