在转换为DTSX之前，在转换为Excel之前修改查询结果的最佳方法

Question

我有一个执行各种任务的DTSX，其中之一是从查询中检索数据并将其转换为保存在Excel文件中 有些行有一个带有重复数据的列，我应该做的是对上述列进行更改

（如前所述的列是黄色）

之后（所需的结果是两个灰色单元格）

我认为，如果我已经有一个带有相同名称的帐户添加后缀_2的帐户，那么在当前行上，假设上游我无法阻止这种情况，那么最好的方法是什么？ （我想在检索数据的查询中，可以做任何事情）

更新 我尝试了此查询，但结果没有改变

WITH CTE as
(
select
    pe.Code,
    pe.Name
    Fiscal_Code, 
    LastName, 
    FirstName, 
    Account,
    ROW_NUMBER()
    OVER( PARTITION By Account Order By LastName,FirstName) as RN
from 
    MyTable mt (nolock)
inner join
    People pe (nolock)
on
    (ltrim(rtrim(mt.Profile))+' '+ltrim(rtrim(mt.House)))=SUBSTRING(ltrim(rtrim(pe.FirstName)),1,11)
where
    flag_new= 1 and
    pe.Code='A' and
    SUBSTRING(ltrim(rtrim(pe.FirstName)),11,1)<>'-' and
    ISNUMERIC(SUBSTRING(ltrim(rtrim(pe.FirstName)),11,1))=1

union


select
    pe.Code,
    pe.Name
    Fiscal_Code, 
    LastName, 
    FirstName, 
    Account,
    ROW_NUMBER()
    OVER( PARTITION By Account Order By LastName,FirstName) as RN
from 
    MyTable mt (nolock)
inner join
    People pe (nolock)
on
    (ltrim(rtrim(mt.Profile))+' '+ltrim(rtrim(mt.House)))=SUBSTRING(ltrim(rtrim(pe.FirstName)),1,11)
where
    flag_new= 1 and
    pe.Code='A' and
    SUBSTRING(ltrim(rtrim(pe.FirstName)),11,1)<>'-' and
    ISNUMERIC(SUBSTRING(ltrim(rtrim(pe.FirstName)),11,1))=0

union


select
    
    pe.Code,
    pe.Name
    Fiscal_Code, 
    LastName, 
    FirstName, 
    Account,
    ROW_NUMBER()
    OVER( PARTITION By Account Order By LastName,FirstName) as RN
from 
    MyTable mt (nolock)
inner join
    People pe (nolock)
on
    (ltrim(rtrim(mt.Profile))+' '+ltrim(rtrim(mt.House)))=SUBSTRING(ltrim(rtrim(pe.FirstName)),1,10)
where
    flag_new= 1 and
    pe.Code='A' and
    SUBSTRING(ltrim(rtrim(pe.FirstName)),11,1)='-'


union

select 
    pe.Code,
    pe.Name
    Fiscal_Code, 
    LastName, 
    FirstName, 
    Account,
    ROW_NUMBER()
    OVER( PARTITION By Account Order By LastName,FirstName) as RN
from 
    MyTable mt (nolock)
inner join
    People pe (nolock)
on
    Code='A'
where
    flag_new= 1 and
    ltrim(rtrim(mt.Profile))= 'OFFICE 1' and
    pe.Type in ('30','31') and
    ((pe.end_validation_date is null) or (pe.end_validation_date>getdate())))
    SELECT
    pe.Code,
    pe.Name
    Fiscal_Code, 
    LastName, 
    FirstName, 
    Account+
    Case 
    when RN = 1
    then ''
    else '_' + cast(RN+1 as varchar(20))
    end as Account
    FROM CTE;

Answer 1

也许可以通过使用变量对象类型和脚本组件来完成

一个非常糟糕的解决方案，即将数据与字符串变量进行比较，并在变量不存在时将其添加到该变量中，并将所需的前缀添加到该变量（如果存在） 。
您可以使用表达任务来完成。

@[User::nametest] = FINDSTRING(@[User::nametest],","+ @[User::V_shape] +",",1)  == 0 ? @[User::nametest] + ","+ @[User::V_shape] +"," : @[User::nametest]

该解决方案可能适合少量记录，但是对于大量记录，这是一个非常糟糕的解决方案