How do I modify the column having 2,4,5 as values to 2=active, 4=closd, and 5=inactv in SSIS derived column? (SQL Server DB)

I'm expecting the column should show values.

'active' instead of '2'
'closd' instead of '4'
'inactv' instead of '5'

CodePudding user response：

your data

declare @a table( c varchar(100))
insert into @a
(c) values 
('2,4,5');
declare @equivalenttable table( id varchar(100),equivalent  varchar(100) )
insert into @equivalenttable
(id,equivalent) values 
('2' ,'active'),
('4' ,'closd'),
('5' ,'inactv');

first use string_split and Cross apply for split string, second use join with equivalent table and third use string_agg as follows:

select 
  string_agg(equivalent, ',') within group (
    order by 
      id
  ) c 
from 
  (
    select 
      a1.value 
    from 
      @a CROSS APPLY STRING_SPLIT(c, ',') a1
  ) a2 
  join @equivalenttable e on a2.value = e.id

dbfiddle

CodePudding user response：

You should use the conditional operator (?:) as follows:

[inputcolumn] == "2" ? "active" : 
[inputcolumn] == "4" ? "closed" : 
[inputcolumn] == "5" ? "inactive" : 
""