By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Documents(DocumentID int, Filename varchar(50));
create table VariableValues(VariableID int,DocumentID int, ValueCache varchar(50), ConfigurationID int);
create table Configuration(ConfigurationID int, ConfigName varchar(50));
insert into Documents values(1,'file1.txt');
insert into VariableValues values
(55,1,'Val1',1),
(56,1,'Val2',1),
(172,1,'Val3',1),
(55,1,'Val1',2),
(56,1,'Val2',2),
(172,1,'Val3',2);
insert into Configuration values
(1,'Default'),
(2,'First');
9 rows affected
select Filename, Val1 as att1, Val2 as att2, Val3 as att3, ConfigName from
(
select Documents.Filename, VariableValues.ValueCache,Configuration.ConfigName
from
Documents inner join VariableValues
on Documents.DocumentID=VariableValues.DocumentID
inner join Configuration
on Configuration.ConfigurationID = VariableValues.ConfigurationID
) D
PIVOT
(MAX(ValueCache) for ValueCache in (Val1, Val2, Val3)) P
| Filename | att1 | att2 | att3 | ConfigName |
|---|---|---|---|---|
| file1.txt | Val1 | Val2 | Val3 | Default |
| file1.txt | Val1 | Val2 | Val3 | First |
DECLARE @valCols AS NVARCHAR(MAX), @sqlQr AS NVARCHAR(MAX)
select @valCols = STUFF((SELECT distinct ',' + QUOTENAME(ValueCache)
from VariableValues FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @sqlQr = 'SELECT Filename, ' + @valCols + ', ConfigName from
(
select Documents.Filename, VariableValues.ValueCache,Configuration.ConfigName
from
Documents inner join VariableValues
on Documents.DocumentID=VariableValues.DocumentID
inner join Configuration
on Configuration.ConfigurationID = VariableValues.ConfigurationID
) D
PIVOT
(MAX(ValueCache) for ValueCache in ('+ @valCols+ ')) P'
execute(@sqlQr)
| Filename | Val1 | Val2 | Val3 | ConfigName |
|---|---|---|---|---|
| file1.txt | Val1 | Val2 | Val3 | Default |
| file1.txt | Val1 | Val2 | Val3 | First |