SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TblSet1_A (
  ID INT,
  KEY_VAL INT,
  VALUE INT
);

CREATE TABLE TblSet1_B (
  ID INT,
  KEY_VAL INT,
  VALUE VARCHAR(255)
);

CREATE TABLE TblSet1_C (
  ID INT,
  KEY_VAL INT,
  VALUE VARCHAR(1000)
);

CREATE TABLE TblSet2_A (
  ID INT,
  KEY_VAL INT,
  VALUE INT
);

CREATE TABLE TblSet2_B (
  ID INT,
  KEY_VAL INT,
  VALUE VARCHAR(255)
);

CREATE TABLE TblSet2_C (
  ID INT,
  KEY_VAL INT,
  VALUE VARCHAR(1000)
);

CREATE TABLE TblLink (
  KEY_VAL INT,
  TYPE INT,
  DATATYPE INT,
  LENGTH INT,
  DESCRIPTION VARCHAR(1000)
);

CREATE TABLE TblA (
  ID INT,
  LINKID INT,
  OTHER VARCHAR(255)
);

CREATE TABLE TblB (
  ID INT,
  LINKID INT,
  OTHER VARCHAR(255)
);

INSERT INTO TblSet1_A (ID, KEY_VAL, VALUE) VALUES
(1, 5, 30),
(2, 5, 45),
(2, 10, 60);

INSERT INTO TblSet1_B (ID, KEY_VAL, VALUE) VALUES
(1, 4, 'Value 4'),
(2, 9, 'Value 9');

INSERT INTO TblSet1_C (ID, KEY_VAL, VALUE) VALUES
(1, 3, 'Value 3'),
(2, 8, 'Value 8');

INSERT INTO TblSet2_A (ID, KEY_VAL, VALUE) VALUES
(1, 2, 20),
(2, 7, 50);

INSERT INTO TblSet2_B (ID, KEY_VAL, VALUE) VALUES
(1, 15, 'Value 15'),
(2, 15, 'Value 15_2'),
(2, 16, 'Value 16');

INSERT INTO TblSet2_C (ID, KEY_VAL, VALUE) VALUES
(1, 17, 'Value 17'),
(2, 18, 'Value 18');

INSERT INTO TblLink (KEY_VAL, TYPE, DATATYPE, LENGTH, DESCRIPTION) VALUES
(2, 2, 1, 200, 'Item 2'),
(3, 1, 2, 1000, 'Item 3'),
(4, 1, 2, 200, 'Item 4'),
(5, 1, 1, 200, 'Item 5'),
(7, 2, 1, 200, 'Item 7'),
(8, 1, 2, 1000, 'Item 8'),
(9, 1, 2, 200, 'Item 9'),
(10, 1, 1, 200, 'Item 10'),
(15, 2, 2, 200, 'Item 15'),
(16, 2, 2, 200, 'Item 16'),
(17, 2, 2, 1000, 'Item 17'),
(18, 2, 2, 1000, 'Item 18');

INSERT INTO TblA (ID, LINKID, OTHER) VALUES
(1, 100, 'ID1'),
(2, 200, 'ID2');

INSERT INTO TblB (ID, LINKID, OTHER) VALUES
(1, 100, 'ID3'),
(2, 200, 'ID4'),
(3, 200, 'ID5');

31 rows affected

A_ID	LINKID	B_ID	OTHER	Item_2	Item_3	Item_4	Item_5	Item_7	Item_8	Item_9	Item_10	Item_15	Item_16	Item_17	Item_18
1	100	1	ID3	20	null	Value 4	30	null	null	null	null	Value 15	null	null	null
2	200	2	ID4	null	null	null	45	50	null	Value 9	60	Value 15_2	Value 16	null	null
2	200	3	ID5	null	null	null	45	null	null	Value 9	60	null	null	null	null