Sql Dynamic Pivot For Multiple Columns

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(Symbol) from Opt FOR XML PATH(''), TYPE ).v

Solution 1:

Just a minor twist to your original. Notice the sub-query/Union All.

Declare@SQLvarchar(max)
Select@SQL= Stuff((SelectDistinct','+ QuoteName(Symbol+'_Price') 
                           +','+ QuoteName(Symbol+'_Quantity') 
                      From   Opt For XML Path('')),1,1,'')   
Select@SQL='Select Date,'+@SQL+' 
                From (
                      Select Date,Item=Symbol+''_Price'',Val=Price From Opt
                      Union All
                      Select Date,Item=Symbol+''_Quantity'',Val=Quantity From Opt
                     ) A
                Pivot (max(Val) For Item in ('+@SQL+') ) p'Exec(@SQL);

Returns

Date         apple_Price    apple_Quantity  banana_Price    banana_Quantity
2016-11-1430152220