您首先需要 UNPIVOT 你的表.您可以使用此查询来完成:

You need first to UNPIVOT your table. You can do it using this query:

SELECT Portfolio, [Date], Val, ColType
FROM (SELECT Portfolio, 
             [Date], 
             TotalLoans, 
             ActiveLoans, 
             TotalBalance 
      FROM mytable
      WHERE Portfolio = 'P1') AS srcUnpivot
      UNPIVOT (
         Val FOR ColType IN (TotalLoans, ActiveLoans, TotalBalance)) AS unpvt

输出:

Portfolio   Date      Val      ColType
===============================================
P1         2015-12-31 1000     TotalLoans
P1         2015-12-31 900      ActiveLoans
P1         2015-12-31 100000   TotalBalance
P1         2015-11-30 1100     TotalLoans
P1         2015-11-30 800      ActiveLoans
P1         2015-11-30 100100   TotalBalance
P1         2015-10-31 1200     TotalLoans
P1         2015-10-31 700      ActiveLoans
P1         2015-10-31 100200   TotalBalance

注意:所有非透视字段必须是相同类型.上面的查询假定所有字段的类型都是 int.如果不是这种情况，则必须使用 CAST.

Note: All unpivoted fields must be of the same type. The query above assumes a type of int for all fields. If this is not the case then you have to use CAST.

使用上面的查询，您可以应用PIVOT:

Using the above query you can apply PIVOT:

SELECT Portfolio, ColType, [2015-12-31], [2015-11-30], [2015-10-31]
FROM (
   ... above query here ...
PIVOT (
   MAX(Val) FOR [Date] IN ([2015-12-31], [2015-11-30], [2015-10-31])) AS pvt