SQL Server 2022

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