I want to give the DDT like an ID when the DDT is "000A" then the new column = 100 and when the DDT is "000B" then the new column = 200 but the problem is I have like 1000 different DDT rows
I have a table like this

and I would like it to come out like this:

CodePudding user response：

Just use a CASE expression:

SELECT sy3, DDT, EXT, CASE DDT WHEN '000A' THEN 100 WHEN '000B' THEN 200 END AS DDT_EXT
FROM yourTable;

Note that you may also wish at some point to have a formal table which can map from DDT values to DDT_EXT values, something like this:

DDT  | DDT_EXT
000A | 100
000B | 200

CodePudding user response：

You can create a mapping table and use that mapping table in the JOIN as given below:

DECLARE @table table(y3 int, DDT    char(4), EXT int)
DECLARE @tableExtMapping table (DDT char(4), DDT_EXT int)
insert into @table 
values
(12 ,'000A',    35)
,(15    ,'000B',    40)
,(16    ,'000A',    12)
,(11    ,'000A',    30)

insert into @tableExtMapping
values ('000A',100),('000B',200);

SELECT      t.*, e.DDT_EXT
FROM        @table as t
INNER JOIN  @tableExtMapping as e
ON          e.DDT = t.DDT