SQL Server 2019

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

CREATE table Campaign(
Id int,
Label nvarchar(50),
);
INSERT INTO Campaign VALUES 
(1, 'Cmp1'), 
(2, 'Cmp2'), 
(3, 'Cmp3');

CREATE table Mission(
Id int,
Label nvarchar(50),
CampaignId int,
);
INSERT INTO Mission VALUES 
(1, 'Mission1-1', 1), 
(2, 'Mission1-2', 1), 
(3, 'Mission2-1', 2),
(4, 'Mission3-1', 3);

CREATE table CampaignAttr(
Id int,
Label nvarchar(50),
CampaignId int,
);
INSERT INTO CampaignAttr VALUES
(1, 'Attr1', 1),
(2, 'Attr2', 1),
(3, 'Attr2', 2),
(4, 'Attr1', 2),
(5, 'Attr3', 2);

CREATE table MissionAttr(
Id int,
MissionId int,
CampaignAttrId int,
Value nvarchar(50)
);
INSERT INTO MissionAttr VALUES
(1, 1, 1, 'val1-1'),
(2, 1, 2, 'val1-2'),
(3, 2, 1, 'val2-1'),
(4, 2, 2, 'val2-2'),
(5, 3, 3, 'val3-3'),
(5, 3, 4, 'val3-4'),
(5, 4, 5, 'val4-5');
19 rows affected

SELECT MissionID, CampaignId, Attr1, Attr2, Attr3
FROM
    (SELECT MissionID, CampaignId, Label, Value FROM MissionAttr m
     JOIN CampaignAttr c ON m.CampaignAttrid = c.id) AS Source
PIVOT(
      MAX(Value)
      for Label IN (Attr1, Attr2, Attr3)
)AS pivottable;


MissionID
CampaignId
Attr1
Attr2
Attr3


1
1
val1-1
val1-2
null

2
1
val2-1
val2-2
null

3
2
val3-4
val3-3
null

4
2
null
null
val4-5


 

MissionID	CampaignId	Attr1	Attr2	Attr3
1	1	val1-1	val1-2	null
2	1	val2-1	val2-2	null
3	2	val3-4	val3-3	null
4	2	null	null	val4-5