I've got this table,
Name Rating
A 2
B 1
C 5
D 3
E 1
F 4
and I've got a rating system
1-Excellent, 2-Very Good, 3-Good, 4-OK, 5-Poor
I was wondering if i could replace the nueric values in the table to get the following result table.
Name Rating
A Very Good
B Excellent
C Poor
D Good
E Excellent
F OK
Thanks
I don't think it's good idea to update your data inplace, it's better to store id of the rating and not text representation of the data. But you can query your table and replace int with text:
select
Name,
case Rating
when 1 then 'Excellent'
when 2 then 'Very Good,'
when 3 then 'Good'
when 4 then 'OK'
when 5 then 'Poor'
end as Rating
from <your table>
Or you can create a lookup table and join with it
create table Rating (id int, desc nvarchar(128))
insert into Rating (id, desc)
select 1, 'Excellent' union all
select 2, 'Very good' union all
select 3, 'Good' union all
select 4, 'OK' union all
select 5, 'Poor'
select
t.Name,
R.desc as Rating
from <your table> as t
left outer join Rating as R on R.id = t.Rating
Use a CASE statement. Of course, this will only work if your column is not set to a numeric value.
UPDATE tblRatings
SET Rating = CASE WHEN 1 THEN 'Excellent'
WHEN 2 THEN 'Very Good'
WHEN 3 THEN 'Good'
WHEN 4 THEN 'OK'
ELSE 'Poor'
END
If it is, you'll need to use a SELECT statement;
SELECT CASE WHEN 1 THEN 'Excellent'
WHEN 2 THEN 'Very Good'
WHEN 3 THEN 'Good'
WHEN 4 THEN 'OK'
ELSE 'Poor'
END
FROM tblRatings
Try using Choose
IF sql-server 2012
like this:
select Rating,name,choose(Rating,'Excellent','Very Good','Good','OK','Excellent','Poor') from table
Tags ! but yes should have mentioned sql-server 2012 thanks!You can use update function :
update yourtable set rating = 'Excellent' where rating = '1'
and zou can do this update for all your ratings
Update YOURTABLE
set Rating = case when '2' then ' Very good'
when '3' then ' good' end
