By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create table Class (
Class_Auto_ID int,
Class_Key nvarchar(30),
Class_ID nvarchar(30),
Class_Name nvarchar(30));
insert into Class values
(1,'Item_Type','A','A'),
(3,'Item_Brand','AA','AA'),
(2,'Item_Type','B','B');
Select *
From Class;
| Class_Auto_ID | Class_Key | Class_ID | Class_Name |
|---|---|---|---|
| 1 | Item_Type | A | A |
| 3 | Item_Brand | AA | AA |
| 2 | Item_Type | B | B |
Create table Item (
Item_ID int,
Item_Name int,
Item_Type_ID nvarchar(30),
Item_Brand_ID nvarchar(30));
insert into Item values
(2,2,'','AA'),
(4,4,'',''),
(1,1,'A',''),
(3,3,'B','AA');
Select *
From Item;
| Item_ID | Item_Name | Item_Type_ID | Item_Brand_ID |
|---|---|---|---|
| 2 | 2 | AA | |
| 4 | 4 | ||
| 1 | 1 | A | |
| 3 | 3 | B | AA |
Select M.Item_ID,M.Item_Name,M.Item_Type_ID,
case When (R.Class_Key = 'Item_Type') and (Q.Class_Key = 'Item_Brand') then R.Class_Name else '' end as Item_Type_Name,
M.Item_Brand_ID,
case When (R.Class_Key = 'Item_Type') and (Q.Class_Key = 'Item_Brand') then Q.Class_Name else '' end as Item_Brand_Name
From Item AS M
Left join Class As R on (R.Class_ID = M.Item_Type_ID) and (R.Class_Key = 'Item_Type')
Left join Class As Q on (Q.Class_ID = M.Item_Brand_ID) and (Q.Class_Key = 'Item_Brand')
| Item_ID | Item_Name | Item_Type_ID | Item_Type_Name | Item_Brand_ID | Item_Brand_Name |
|---|---|---|---|---|---|
| 2 | 2 | AA | |||
| 4 | 4 | ||||
| 1 | 1 | A | |||
| 3 | 3 | B | B | AA | AA |