How to replace values with two tables in SQL?-CodePudding

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

CodePudding user response：

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
;

CodePudding user response：

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