I can think of at least two options…
CREATE TABLE constants ( id AS INT, Illustrated AS VARCHAR(3), FrontPage AS VARCHAR(3), BackPage AS VARCHAR(3), EDLP AS VARCHAR(3), SpecialPromo AS VARCHAR(3) ) INSERT INTO constants SELECT 1, 'I', 'FP', 'BP', 'ELP', 'PR' SELECT Name, CASE WHEN CHARINDEX(constants.Illustrated, data.S_Data) > 0 THEN 1 ELSE 0 END AS Illustrated, etc, etc FROM data INNER JOIN constants ON constants.id = 1
Or…
CREATE TABLE constants ( constant_set_id AS INT, constant_name AS VARCHAR(16), value AS AS VARCHAR(3) ) INSERT INTO constants SELECT 1, 'Illustrated', 'I' INSERT INTO constants SELECT 1, 'FrontPage', 'FP' INSERT INTO constants SELECT 1, 'BackPage', 'BP' INSERT INTO constants SELECT 1, 'EDLP', 'ELP' INSERT INTO constants SELECT 1, 'SpecialPromo', 'PR' SELECT Name, MAX(CASE WHEN constants.constant_name = 'Illustrated' AND CHARINDEX(constants.value, data.S_Data) > 0 THEN 1 ELSE 0 END) AS Illustrated, etc, etc FROM data INNER JOIN constants ON constants.constant_set_id = 1 GROUP BY data.name
Both let you have multiple different sets of constants. One is expandable without changing the schema, though the query still would need to change.
The main advantage of either approach is that you can re-use the constants else where, but store them once in a centralised location. Which is only relevant if/when the values in the constants needs updating. Re-use through indirection.ShareImprove this answerFollow