ScottPletcher (10/20/2014)
If the controlling column(s) are all from the same table, I suggest a computed column. That leaves the defining logic in only a single place, and allows the calc'd value to be used just like any other column: in SELECT list, WHERE clause, ORDER BY clause, etc..
ALTER dbo.target_table
ADD target_column AS
case
when source_column_1 = 'ABC' then 'XYZ'
when source_column_2 = '123' then 'PDQ'
when source_column_3 = 'DEF' then 'BLAH'
when source_column_3 = 'FOO' then 'Fighters'
when source_column_3 = 'DOG' then 'CAT'
when source_column_3 = 'RED' then 'Blue'
when source_column_3 in ('UP','DOWN') then 'Strange'
when source_column_4 = 'ICE' then 'Tea'
when source_column_4 = 'PHX' then 'Phoenix'
when source_column_4 = 'YES' then 'Indeed'
when source_column_4 in ('CAR','BOS') then 'Stuff'
end
Then, for example:
SELECT target_column, ...
FROM ...
WHERE
target_column IN ('Tea', 'Indeed')
ORDER BY
target_column
Nice idea, but I can't see how such a solution can reasonably be offered to business users, since it requires an ALTER TABLE every time the user wishes to update the meta-data.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.