SQL Server 2017

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

CREATE TABLE FileTable ( Id int, Name varchar(20) ) ;
INSERT INTO FileTable (Id, Name)
VALUES ( 1, 'File1.docx' ), ( 2, 'File2.xlsx' ), ( 3, 'File3.pdf' )
;
3 rows affected

CREATE TABLE TagTable ( Id int, Name varchar(20) ) ;
INSERT INTO TagTable (Id, Name)
VALUES 
    ( 1, 'Tag1' )
  , ( 2, 'Tag2' )
  , ( 3, 'Tag3' )
  , ( 4, 'Tag4' )
  , ( 5, 'Tag5' )
  , ( 6, 'Tag6' )
;
6 rows affected

CREATE TABLE TagValueTable ( FileId int, TagId int, theValue varchar(20) );
INSERT INTO TagValueTable (FileId, TagId, theValue)
VALUES 
    (1,1,'file1tag1value')
  , (1,2,'file1tag2value')
  , (1,4,'file1tag4value')
  , (2,1,'file2tag1value')
  , (2,4,'file2tag4value')
;  
5 rows affected

SELECT ft.Name, ca.TagName, tvt.theValue 
FROM FileTable ft
CROSS APPLY (
  SELECT tt.ID AS TagID, tt.Name AS TagName
  FROM TagTable tt
) ca
LEFT OUTER JOIN TagValueTable tvt ON ft.ID = tvt.FileID
    AND ca.TagID = tvt.TagID


Name
TagName
theValue


File1.docx
Tag1
file1tag1value

File1.docx
Tag2
file1tag2value

File1.docx
Tag3
null

File1.docx
Tag4
file1tag4value

File1.docx
Tag5
null

File1.docx
Tag6
null

File2.xlsx
Tag1
file2tag1value

File2.xlsx
Tag2
null

File2.xlsx
Tag3
null

File2.xlsx
Tag4
file2tag4value

File2.xlsx
Tag5
null

File2.xlsx
Tag6
null

File3.pdf
Tag1
null

File3.pdf
Tag2
null

File3.pdf
Tag3
null

File3.pdf
Tag4
null

File3.pdf
Tag5
null

File3.pdf
Tag6
null

Name	TagName	theValue
File1.docx	Tag1	file1tag1value
File1.docx	Tag2	file1tag2value
File1.docx	Tag3	null
File1.docx	Tag4	file1tag4value
File1.docx	Tag5	null
File1.docx	Tag6	null
File2.xlsx	Tag1	file2tag1value
File2.xlsx	Tag2	null
File2.xlsx	Tag3	null
File2.xlsx	Tag4	file2tag4value
File2.xlsx	Tag5	null
File2.xlsx	Tag6	null
File3.pdf	Tag1	null
File3.pdf	Tag2	null
File3.pdf	Tag3	null
File3.pdf	Tag4	null
File3.pdf	Tag5	null
File3.pdf	Tag6	null