Let's start by correcting your DDL and sample data so that it will run in MS SQL (converted to temp tables to reduce clutter in my sandbox):

CREATE TABLE #TABLE_1

(

Field1 VARCHAR(4) NULL, Field2 VARCHAR(4) NULL, Field3 VARCHAR(4) NULL

);

INSERT INTO #TABLE_1 (Field1, Field2, Field3)

VALUES ('A', 'B', NULL),(NULL, 'A', NULL),('A', 'B', 'C'),(NULL, NULL, NULL);

CREATE TABLE #TABLE_2

(

FieldName VARCHAR(4) NOT NULL, Title VARCHAR(20) NOT NULL);

INSERT INTO #TABLE_2 (FieldName, Title)

Values ('A', 'xyz'),('D', 'yyy'),('B','zzz');

Next we'll apply the CROSS APPLY VALUES approach to UNPIVOT from #TABLE_1, eliminating NULLs and then doing a JOIN to #TABLE_2.

SELECT b.Field, c.Title

FROM #TABLE_1 a

CROSS APPLY (

VALUES (Field1),(Field2),(Field3)) b (Field)

INNER JOIN #Table_2 c ON b.Field = c.FieldName

WHERE Field IS NOT NULL;

Go

DROP TABLE #Table_1;

DROP TABLE #Table_2;

The CAV approach to UNPIVOT is explained in the first of my signature links below.