从原始数据创建n个新行，例如(1000 .... 1000 + n) [英] Create n new rows from raw data such as (1000....1000+n)

问题描述

我需要从Excel工作簿中读取数据，数据以这种方式存储在其中:

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)

在SSIS中使用OLE DB目标的预期输出为:

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?

推荐答案

首先，必须将数据插入到某些临时表中. 这里是几种方式.然后运行以下查询:

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)

这篇关于从原始数据创建n个新行，例如(1000 .... 1000 + n)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！