--DDL
CREATE TABLE MainTable_T(
	Movie VARCHAR(30),
	MainTable_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED
)
CREATE TABLE AttributeType_T(
	AttributeType VARCHAR(30),
	AttributeType_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED
)
CREATE TABLE AttributeValue_T(
	MainTable_ID INT NOT NULL,
	AttributeType_ID INT NOT NULL,
	AttributeValue VARCHAR(30),
	CONSTRAINT PK_AttributeValue_T PRIMARY KEY NONCLUSTERED (MainTable_ID, AttributeType_ID)
)
GO
--DML
INSERT INTO MainTable_T VALUES ('Star Trek')
INSERT INTO MainTable_T VALUES ('Star Wars')
INSERT INTO MainTable_T VALUES ('Battlestar Galactica')

INSERT INTO AttributeType_T VALUES ('Vulcans')
INSERT INTO AttributeType_T VALUES ('Light Sabers')
INSERT INTO AttributeType_T VALUES ('Laser Pistols')
GO
INSERT INTO AttributeValue_T VALUES (1,1,'Y')
INSERT INTO AttributeValue_T VALUES (1,3,'Y')
INSERT INTO AttributeValue_T VALUES (2,2,'Y')
INSERT INTO AttributeValue_T VALUES (2,3,'Y')
INSERT INTO AttributeValue_T VALUES (3,3,'Y')
GO

--See what the data looks like
SELECT  m.MainTable_ID AS MovieID, m.Movie, at.AttributeType_ID AS AttributeID, at.AttributeType, av.AttributeValue
INTO Movies_T
FROM    MainTable_T m
LEFT OUTER JOIN AttributeValue_T av ON m.MainTable_ID = av.MainTable_ID
INNER JOIN AttributeType_T at ON av.AttributeType_ID = at.AttributeType_ID
GO
SELECT * FROM Movies_T

--Original attempt
SELECT m.Movie, avV.AttributeValue AS Vulcans, avP.AttributeValue AS LaserPistols, avS.AttributeValue AS LightSabers
  FROM MainTable_T m
  LEFT OUTER JOIN AttributeValue_T avV ON m.MainTable_ID = avV.MainTable_ID
  LEFT OUTER JOIN AttributeType_T  atV ON avV.AttributeType_ID = atV.AttributeType_ID AND atV.AttributeType = 'Vulcans'
  LEFT OUTER JOIN AttributeValue_T avP ON m.MainTable_ID = avP.MainTable_ID
  LEFT OUTER JOIN AttributeType_T  atP ON avP.AttributeType_ID = atP.AttributeType_ID AND atP.AttributeType = 'Laser Pistols'
  LEFT OUTER JOIN AttributeValue_T avS ON m.MainTable_ID = avS.MainTable_ID
  LEFT OUTER JOIN AttributeType_T  atS ON avS.AttributeType_ID = atS.AttributeType_ID AND atS.AttributeType = 'Light Sabers'
  
--Frist attempt with JOIN
SELECT 'name' AS ID, [1], [2], [3]
FROM
(SELECT m.Movie, m.MainTable_ID, av.AttributeType_ID, at.AttributeType
	FROM    MainTable_T m
	LEFT OUTER JOIN AttributeValue_T av ON m.MainTable_ID = av.MainTable_ID
	INNER JOIN AttributeType_T at ON av.AttributeType_ID = at.AttributeType_ID ) AS SourceTable
PIVOT
(
max(at.AttributeType)
FOR av.AttributeType_ID  IN ([1], [2], [3])
) AS PivotTable

SELECT  m.Movie,
        MAX(CASE WHEN AttributeType = 'Vulcans' THEN 'Y'
                 ELSE ''
            END) AS Vulcans
FROM    MainTable_T m
        JOIN AttributeValue_T av ON m.MainTable_ID = av.MainTable_ID
        JOIN AttributeType_T at ON av.AttributeType_ID = at.AttributeType_ID
GROUP BY Movie;

CREATE TABLE MainTable_T(
    Movie VARCHAR(30),
    MainTable_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED
)
GO

CREATE TABLE AttributeType_T(
    AttributeType VARCHAR(30),
    AttributeType_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED
)
GO

CREATE TABLE AttributeValue_T(
    MainTable_ID INT NOT NULL,
    AttributeType_ID INT NOT NULL,
    AttributeValue VARCHAR(30),
    CONSTRAINT PK_AttributeValue_T PRIMARY KEY NONCLUSTERED (MainTable_ID, AttributeType_ID)
)
GO

INSERT INTO MainTable_T VALUES ('Star Trek')
INSERT INTO MainTable_T VALUES ('Star Wars')
INSERT INTO MainTable_T VALUES ('Battlestar Galactica')

INSERT INTO AttributeType_T VALUES ('Vulcans')
INSERT INTO AttributeType_T VALUES ('Light Sabers')
INSERT INTO AttributeType_T VALUES ('Laser Pistols')
GO
INSERT INTO AttributeValue_T VALUES (1,1,'Y')
INSERT INTO AttributeValue_T VALUES (1,3,'Y')
INSERT INTO AttributeValue_T VALUES (2,2,'Y')
INSERT INTO AttributeValue_T VALUES (2,3,'Y')
INSERT INTO AttributeValue_T VALUES (3,3,'Y')
GO

CREATE NONCLUSTERED INDEX [IX_AttributeValue_Cover] ON [dbo].[AttributeValue_T] 
(
    [MainTable_ID] ASC,
    [AttributeType_ID] ASC,
    [AttributeValue] ASC
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_MainTable_T_Movie_MainTableID] ON [dbo].[MainTable_T] 
(
    [MainTable_ID] ASC,
    [Movie] ASC
) ON [PRIMARY]
GO

SELECT 
     Result.Movie
    ,ISNULL(Result.[Vulcans],'') AS [Vulcans]
    ,ISNULL(Result.[Light Sabers],'') AS [LightSabers]
    ,ISNULL(Result.[Laser Pistols],'') AS [LaserPistols]
FROM
    (
    SELECT  
         m.Movie
        ,at.AttributeType
        ,av.AttributeValue
    FROM
        MainTable_T m
    INNER JOIN 
        AttributeValue_T av 
        ON m.MainTable_ID = av.MainTable_ID
    INNER JOIN 
        AttributeType_T at 
        ON av.AttributeType_ID = at.AttributeType_ID
    WHERE
        m.MainTable_ID > 0
    ) M
    PIVOT
    (MAX(AttributeValue) FOR AttributeType IN ([Vulcans],[Light Sabers],[Laser Pistols]))
    AS Result

SELECT  
     m.Movie
    ,MAX(CASE WHEN AttributeType = 'Vulcans' THEN 'Y'
              ELSE ''
        END) AS Vulcans
    ,MAX(CASE WHEN AttributeType = 'Light Sabers' THEN 'Y'
              ELSE ''
        END) AS LightSabers
    ,MAX(CASE WHEN AttributeType = 'Laser Pistols' THEN 'Y'
              ELSE ''
        END) AS LaserPistols
FROM
    MainTable_T m
    INNER JOIN 
        AttributeValue_T av 
        ON m.MainTable_ID = av.MainTable_ID
    INNER JOIN 
        AttributeType_T at 
        ON av.AttributeType_ID = at.AttributeType_ID
WHERE
    m.MainTable_ID > 0
GROUP BY Movie

Movie			Vulcans		LightSabers	LaserPistols
Battlestar Galactica					Y
Star Trek		Y				Y
Star Wars				Y		Y

Declare @sql Varchar(MAX) 
Select @sql = 'Select m.Movie, ' + STUFF((Select DISTINCT ',MAX(Case When at.AttributeType = ' + CHAR(39) + at.AttributeType + CHAR(39) + ' Then av.AttributeValue Else '''' End) As [' + at.AttributeType + ']' From MainTable_T m LEFT OUTER JOIN AttributeValue_T av ON m.MainTable_ID = av.MainTable_ID INNER JOIN AttributeType_T at ON av.AttributeType_ID = at.AttributeType_ID For XML PATH('')),1,1,'') 
Select @sql = @sql + ' From MainTable_T m LEFT OUTER JOIN AttributeValue_T av ON m.MainTable_ID = av.MainTable_ID INNER JOIN AttributeType_T at ON av.AttributeType_ID = at.AttributeType_ID Group By m.Movie ' 
Execute(@sql)