SQL Server 2008 - Replacing codes with values in multiple columns

0
votes

I have the following tables:

Table_1:

Value_ID     Value_Code_1      Value_Code_2       Value_Code_3
1            465               325                129

Table_2:

ID          Text
465         this is a value
325         this value is a different one
129         hello world

I need to replace the codes in Table_1 with the Text from Table_2. The way I thought about solving it seems quite long and hairy, is there a quicker, better way of doing this? I have to do this for around 40 columns and not 3 as in my example.

My code:

Select
    first.ID,
    first.string_value,
    second.string_value,
    third.string_value
from
(
    Select
        a.ID,
        b.text as string_value
    From
        table_1 a
        left join
        table_2 b
        on a.Value_Code_1 = b.ID
) first

left join on first.id = second.id

(
    Select
        a.ID,
        b.text as string_value
    From
        table_1 a
        left join
        table_2 b
        on a.Value_Code_2 = b.ID
) second

left join on first.id = third.id
(
    Select
        a.ID,
        b.text as string_value
    From
        table_1 a
        left join
        table_2 b
        on a.Value_Code_2 = b.ID
) third

Thank you.

2
sqlsql-server-2008selectleft-join
You keep saying replacing. I do not think it means what you think it means. - Yuck
@Dems: Yep! I would have called it joining as replacing infers that you're wanting to update the data. Oh, semantics... - Yuck
@yuck - "lookup and use instead"? - MatBailie

2 Answers

2
votes

You can do it simpler, like this:

Select
    Table_1.ID,
    v1.Text,
    v2.Text,
    v3.Text
FROM Table_1
LEFT JOIN Table_2 v1 ON Table_1.Value_code_1 = v1.id
LEFT JOIN Table_2 v2 ON Table_1.Value_code_2 = v2.id
LEFT JOIN Table_2 v3 ON Table_1.Value_code_3 = v3.id;

You still need to do it for all 40 columns.

0
votes

I don't know if this is more or less performant than 40 joins, or if it is easier or not, but it's an option to consider...

SELECT
  Table1.Value_ID,
  MAX(CASE WHEN Table1.Value_Code_1 = Table2.ID THEN Table2.Text ELSE NULL END) AS Text1,
  MAX(CASE WHEN Table1.Value_Code_2 = Table2.ID THEN Table2.Text ELSE NULL END) AS Text2,
  MAX(CASE WHEN Table1.Value_Code_3 = Table2.ID THEN Table2.Text ELSE NULL END) AS Text3,
  MAX(CASE WHEN Table1.Value_Code_4 = Table2.ID THEN Table2.Text ELSE NULL END) AS Text4,
  etc
FROM
  Table1
LEFT JOIN
  Table2
    ON Table2.ID = Table1.Value_Code_1
    OR Table2.ID = Table1.Value_Code_2
    OR Table2.ID = Table1.Value_Code_3
    OR Table2.ID = Table1.Value_Code_4
GROUP BY
  Table1.Value_ID


If you were to normalise your Table1, it becomes even easier...

New Table1:
> Value_ID,
> Column_ID,
> Value_Code

New SQL:
SELECT
  Table1.Value_ID,
  MAX(CASE WHEN Table1.Column_ID = 1 THEN Table2.Text ELSE NULL END) AS Text1,
  MAX(CASE WHEN Table1.Column_ID = 2 THEN Table2.Text ELSE NULL END) AS Text2,
  MAX(CASE WHEN Table1.Column_ID = 3 THEN Table2.Text ELSE NULL END) AS Text3,
  MAX(CASE WHEN Table1.Column_ID = 4 THEN Table2.Text ELSE NULL END) AS Text4,
  etc
FROM
  Table1
LEFT JOIN
  Table2
    ON Table2.ID = Table1.Value_Code
GROUP BY
  Table1.Value_ID

(This is similar to the answer that uses UNPIVOT)