Try this:
DECLARE @tab1 TABLE(UnitTypeId INT, UnitType NVARCHAR(30))
INSERT INTO @tab1 (UnitTypeId, UnitType)
VALUES(1, 'Rolls')
INSERT INTO @tab1 (UnitTypeId, UnitType)
VALUES(2, 'Sheets')
INSERT INTO @tab1 (UnitTypeId, UnitType)
VALUES(3, 'Bobbins')
DECLARE @tab2 TABLE(AttributeId INT, Attribute NVARCHAR(30))
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(1, 'Width')
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(2, 'Diameter')
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(3, 'Comments')
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(4, 'Core Size')
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(5, 'Length')
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(6, 'Diameter Type')
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(7, 'Core Type')
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(8, 'Grain_Direction')
DECLARE @tab3 TABLE (UnitTypeAttributeId INT, UnitTypeId INT, AttributeId INT, DisplayAs NVARCHAR(30))
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(1, 1, 1, 'Roll Width')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(2, 1, 2, 'Roll Diameter')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(3, 1, 3, 'Roll Dia comments')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(4, 1, 4, 'Core size')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(5, 2, 1, 'Sheet width')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(6, 2, 5, 'Sheet Length')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(7, 3, 1, 'Bobbin width')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(8, 3, 5, 'Bobbin Length')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(9, 1, 6, 'Diameter Type')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(10, 1, 7, 'Core Type')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(11, 2, 8, 'Grain Dir')
SELECT UnitType, [Width], [Diameter], [Comments], [Core Size], [Length], [Diameter Type], [Core Type], [Grain_Direction]
FROM (
SELECT t2.UnitType, t3.Attribute, t1.DisplayAs
FROM @tab3 AS t1 INNER JOIN @tab1 AS t2 ON t1.UnitTypeId = t2.UnitTypeId
INNER JOIN @tab2 AS t3 ON t1.AttributeId = t3.AttributeId
) AS DT
PIVOT(MAX(DisplayAs) FOR Attribute IN ([Width], [Diameter], [Comments], [Core Size], [Length], [Diameter Type], [Core Type], [Grain_Direction])) AS PT