>>>SELECT fd.No,
>>> fd.desc,
>>> nf.nutDesc,
>>> nd.nut_val
>>> FROM fdes fd
>>> inner join nutdata nd on nd.No = fd.No
>>> inner join nutrdef nf on nf.No = nd.No
>>>
>>>01001 Butter, salted Protein 0.85 >>>01001 Butter, salted Total fat 81.11 >>>01001 Butter, salted Carbs 0.06 >>>01001 Butter, salted Ash 2.11 >>>>>>How do I construct the query to yield them pivoted like this:
>>>No Desc Protein Total fat Carbs Ash >>>01001 Butter, salted 0,85 81.11 0.06 2.11 >>>>>>
>>declare @Columns nvarchar(max), @Sql nvarchar(max); >> >>select @Columns = stuff((select ', ' + quotename(nutDesc) from (select distinct nutDesc from >> >> FROM fdes fd >> inner join nutdata nd on nd.No = fd.No >> inner join nutrdef nf on nf.No = nd.No >>) X FOR XML PATH(''), type).value('.', 'nvarchar(max)'), 1,2,'') >> >>set @SQL = N'SELECT * FROM (SELECT fd.No, >>fd.desc, >> nf.nutDesc, >> nd.nut_val >> FROM fdes fd >> inner join nutdata nd on nd.No = fd.No >> inner join nutrdef nf on nf.No = nd.No >>) X PIVOT (MAX(nut_Val) FOR NUT_DESC IN (' + @Columns + ')) pvt' >> >>PRINT @SQL; >> >>execute (@SQL)>>