PIVOT without aggregate. Using Max on multiple columns

Question

I have a table with the following columns:

      Year   Cost   Price
      2014    100    200
      2015    150    300
      2016    200    500

I need to transpose the data to:

      Category     2014    2015  2016
       Cost         100     150   200
       Price        200     300   500

I tried to use Pivot on Max(column) but realised I can only do it for one column. I cannot use Max(cost), Max(price).

Any idea how I can achieve this in SQL?

SELECT *
FROM (
      SELECT Year,cost,price
      FROM #t1) up
      PIVOT (Max(cost) FOR year IN ([2014],[2015],[2016])) AS pvt

expected output is:

    Category       2014    2015  2016
       Cost         100     150   200
       Price        200     300   500

Answer 1

Asuming you want dynamic. The trick is to use a Cross Apply

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName([Year]) From Yourtable  Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Select [Category],' + @SQL + '
From (
        Select A.Year
              ,B.*
         From YourTable A
         Cross Apply (Values (''Cost'',A.Cost)
                            ,(''Price'',A.Price) 
                     ) B (Category,Value)
     ) A
 Pivot (max(Value) For [Year] in (' + @SQL + ') ) p'
Exec(@SQL);

Returns

Category    2014    2015    2016
Cost        100     150     200
Price       200     300     500

If not DYNAMIC the SQL Generated

Select [Category],[2014],[2015],[2016]
From (
        Select A.Year
              ,B.*
         From YourTable A
         Cross Apply (Values ('Cost',A.Cost)
                            ,('Price',A.Price) ) B (Category,Value)
     ) A
 Pivot (max(Value) For [Year] in ([2014],[2015],[2016]) ) p

If it helps with the visualization, the sub-query with a cross apply generates this