How to replace values with two tables in SQL?

Question

Assume, I have two HUGHE tables: one is for item id such as 10, 20, etc., and another one is item code. The goal is to replace all item id to its corresponding item code.

Table 1:

SELECT [customer]
      ,[item1]
      ,[item2]
      ,[item3]
      ,[item4]
FROM [StoreData].[dbo].[Customer]

customer	item1	item2	item3	item4
1	10	NULL	20	30
2	NULL	NULL	45	35
3	12	NULL	22	67
4	57	22	20	NULL

Table 2:

SELECT [item]
      ,[code]
FROM [StoreData].[dbo].[ItemCode]

item	code
10	1000
20	1000
30	1000
12	1500
22	1500
33	1500
35	2000
45	2000
57	2300
67	2300

Final table needs to be

customer	item1	item2	item3	item4
1	1000	NULL	1000	1000
2	NULL	NULL	2000	2000
3	1500	NULL	1500	2300
4	2300	1500	10000	NULL

There are numerous items (> 1M rows). Thus it is no way to manually relabel these one by one. How can we do it automatically? Many Thanks

If you normalised your design, you would find this far easier. — Thom A
– Thom A ♦, Commented Feb 22, 2022 at 16:23

Lamak · Accepted Answer · 2022-02-22 16:23:47Z

1

This is why JOINs exists:

SELECT 
    c.[customer],
    ic1.code Item1,
    ic2.code Item2,
    ic3.code Item3,
    ic4.code Item4
FROM [StoreData].[dbo].[Customer] c
LEFT JOIN [StoreData].[dbo].[ItemCode] ic1
    ON c.item1 = ic1.item
LEFT JOIN [StoreData].[dbo].[ItemCode] ic2
    ON c.item2 = ic2.item
LEFT JOIN [StoreData].[dbo].[ItemCode] ic3
    ON c.item3 = ic3.item
LEFT JOIN [StoreData].[dbo].[ItemCode] ic4
    ON c.item4 = ic4.item
;

answered Feb 22, 2022 at 16:23

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

D-Shih · Accepted Answer · 2022-02-22 16:24:05Z

1

You can try aggratage condtion function INNER JOIN tables on items

SELECT t1.customer,
       MAX(CASE WHEN t1.item1 = t2.item THEN t2.code END) item1,
       MAX(CASE WHEN t1.item2 = t2.item THEN t2.code END) item2,
       MAX(CASE WHEN t1.item3 = t2.item THEN t2.code END) item3 ,
       MAX(CASE WHEN t1.item4 = t2.item THEN t2.code END) item4
FROM [Customer] t1
INNER JOIN [ItemCode] t2
ON t2.item IN (t1.item1,t1.item2,t1.item3,t1.item4)
GROUP BY t1.customer

SQLFIDDLE

answered Feb 22, 2022 at 16:24

D-Shih

46.5k6 gold badges38 silver badges56 bronze badges

Collectives™ on Stack Overflow

How to replace values with two tables in SQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related