Correlate these tables dynamically (or automated)

I have a situation where I have a couple of tables (TblA and TblB) that link together to start a record. I then have a set of tables (TblSet1_A, TblSet1_B, TblSet1_C, etc.) that hold values linked by another table, tblLink.

The tblLink has:

1. Key_Val: key value to find in tblSet tables
2. Type: determines which set of tables to look at (1 = TblSet1, 2 = TblSet2, etc.)
3. DataType: determines which table of a set to look at (1=_A, 2=_B or _C)
4. Length: max length of value (if type = 2 and <= 255 then =_B else _C)
5. Description: attribute description

Example TblLink entry:

This would indicate the Key_Val = 4, tblSet table is tblSet1_B, attribute name 'Key_Val 4'.

Example TblSet1_B Entry:

If I manually go through the system and map everything, I can write a query like this.

Note: TblLink Type = 1 indicates matching with TblA.ID, Type = 2 matches with TblB.ID.

SELECT
    a.ID AS A_ID,
    a.LINKID,
    b.ID AS B_ID,  
    b.OTHER,
    (SELECT [Value] 
     FROM TblSet1_B 
     WHERE [ID] = a.ID AND [KEY_VAL] = 4) AS item_4,
    (SELECT [Value] 
     FROM TblSet2_A 
     WHERE [ID] = b.ID AND [KEY_VAL] = 7) AS item_7
FROM 
    TblA a
JOIN
    TblB b ON (a.LINKID = B.LINKID);

Output would be like (using table data not shown above, see full example link):

Is there a way to dynamically do this mapping and build a query in SQL or Transact-SQL (T-SQL) instead of building the (SELECT) statements manually? I'm currently on MS SQL Server 2019.

Please see this dbfiddle link for a full tables and example: https://dbfiddle.uk/Atku04Bo.

This can be done with dynamic SQL, using STRING_AGG to concatenate the dynamic SELECT statements.

DECLARE @sql NVARCHAR(MAX);
DECLARE @selectColumns NVARCHAR(MAX);
DECLARE @fromClause NVARCHAR(MAX);

SET @sql = '
SELECT a.ID AS A_ID, a.LINKID, b.ID AS B_ID, b.OTHER';

SET @fromClause = '
FROM TblA a
JOIN TblB b ON a.LINKID = b.LINKID;';

WITH LinkData AS (
    SELECT
        Key_Val,
        Type,
        DataType,
        Length,
        Description,
        'TblSet' + CAST(Type AS VARCHAR(10)) + 
        CASE DataType 
            WHEN 1 THEN '_A' 
            WHEN 2 THEN '_B' 
            ELSE '_C' END AS TblSetTable,
        CASE Type WHEN 1 THEN 'a.ID' ELSE 'b.ID' END AS IDColumn
    FROM TblLink
)
SELECT @selectColumns = STRING_AGG(
    '(SELECT [Value] FROM ' + TblSetTable + ' WHERE [ID] = ' + IDColumn + ' AND [Key_Val] = ' + CAST(Key_Val AS VARCHAR(10)) + ') AS [Item_' + CAST(Key_Val AS VARCHAR(10)) + ']',
    ', ')
FROM LinkData;

SET @sql = @sql + ', ' + @selectColumns + @fromClause;
EXEC sp_executesql @sql;

Output:

fiddle