I have to convert an int column to a text column and replace the integer values within that column.
For example I have a column status that can contain values like 0, 1, 2, 3, 4, 5, 6, 7.
For '0' I have to replace the value with "New", for '1' with "Identified" and so on.
For this scenario how to write a SQL query?
Personally, I'd go with a mapping table, but another option is CHOOSE() or even the traditional CASE
Note the +1 in the CHOOSE option ... 0 is not a valid option and would return NULL
Example
Declare @YourTable Table ([Status] int)
Insert Into @YourTable Values
(0),(1),(2),(3)
Select *
,ViaCHOOSE = choose([Status]+1,'New','Identified','Some Other','...')
,ViaCASE = case [Status]
when 0 then 'New'
when 1 then 'Identified'
when 2 then 'Some Other'
else null -- or 'Undefined'
end
From @YourTable
Results
Status ViaCHOOSE ViaCASE
0 New New
1 Identified Identified
2 Some Other Some Other
3 ... ...
You could create a (temporary) table with that mapping.
create table XYMapping (number int, text varchar(max));
INSERT INTO XYMapping (number, text) VALUES (1, 'New'), (2, 'Identified'); -- ...
Insert all values and then join them.
Steps to follow to convert int column to text and replace the existing values.
Something like this
drop table if exists #samples;
go
create table #samples (
id int not null,
stat varchar(10) not null);
insert #samples(id, stat) values
(10, 'Start'),
(1, 'Start'),
(1, 'Failed'),
(2, 'Start'),
(3, 'Start'),
(3, 'Failed'),
(4, 'Start'),
(4, 'Failed'),
(4, 'Start'),
(4, 'Failed');
/* step 1: alter the table (and implicitly convert) */
alter table #samples
alter column
id varchar(20) not null;
/* step 2: update based on conversion table */
update s
set id=v.new_str
from #samples s
join
(values ('1', 'New'),
('2', 'Identified'),
('3', 'Identified'),
('4', 'Identified'),
('10', 'Other')) v(old_int, new_str) on s.id=v.old_int;
select * from #samples;
id stat
Other Start
New Start
New Failed
Identified Start
Identified Start
Identified Failed
Identified Start
Identified Failed
Identified Start
Identified Failed