SQL join rows from second table as columns-CodePudding

This is probably a duplicate question, but I couldn't find it. Maybe I'm thinking about this backwards.

How do I format a query to return the results like this with the source tables below?

Filename	Attr1	Attr2	Attr3	Configuration
file1.txt	Val1	Val2	Val3	Default
file1.txt	Val1	Val2	Val3	First

Documents Table

DocumentID	Filename
1	file1.txt

VariableValues table

VariableID	DocumentID	ValueCache	ConfigurationID
55	1	Val1	1
56	1	Val2	1
172	1	Val3	1
55	1	Val1	2
56	1	Val2	2
172	1	Val3	2

Configuration Table

ConfigurationID	ConfigName
1	Default
2	First

I originally wrote this with subqueries

select top 10 d.Filename,   
(select ValueCache from VariableValue vv where d.DocumentID = vv.DocumentID and vv.VariableID = 55) as Description,
(select ValueCache from VariableValue vv where d.DocumentID = vv.DocumentID and vv.VariableID = 56) as PartNumber,
(select ValueCache from VariableValue vv where d.DocumentID = vv.DocumentID and vv.VariableID = 172) as CrossReference,
dc.ConfigurationName
FROM Documents d
INNER JOIN VariableValue vv ON d.DocumentID = vv.DocumentID
inner join DocumentConfiguration dc ON dc.ConfigurationID = vv.ConfigurationID

But that doesn't handle configurations properly, the subqueries return multiple results when there are multiple configurations. So I tried converting it to a table valued query, but this returns each variable on a separate line and I can't figure out how to transpose them.

  select top 10 d.Filename, d.DocumentID, ConfigurationID, d.CurrentStatusID

  into #temp
    FROM Documents d
INNER JOIN VariableValue vv ON d.DocumentID = vv.DocumentID
WHERE vv.VariableID = 172 and ValueCache is not null


select t.Filename, vv.ValueCache, v.VariableName, s.Name from #temp t
inner join VariableValue vv ON t.DocumentID = vv.DocumentID and t.ConfigurationID = vv.ConfigurationID
inner join Variable v ON vv.VariableID = v.VariableID and v.VariableID in (55, 56, 172)
inner join [Status] s ON s.StatusID = t.CurrentStatusID

drop table #temp

CodePudding user response：

You can use the PIVOT function on your INNER JOIN query in two scenarios:

1st one: if the number of ValueCache values is known i.e. only (val1, val2, val3)

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

2nd one: if the number of ValueCache values is unknown then you can use Dynamic SQL Query

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)

See the result form dbfiddle.