SQL Server Pivot on multiple fields

Question

I have searched this website for all possible solutions but still can't find an answer for my Pivot problem.

I have a table with the following data.

Portfolio  |  Date      |  TotalLoans  | ActiveLoans  | TotalBalance
--------------------------------------------------------------------
P1         | 2015-12-31 |       1,000  |         900  |   100,000.00
P1         | 2015-11-30 |       1,100  |         800  |   100,100.00
P1         | 2015-10-31 |       1,200  |         700  |   100,200.00

I am trying to create a pivot with the following output (only where Portfolio = P1)

Field        | 2015-12-31 | 2015-11-30 | 2015-10-31 |
-----------------------------------------------------
TotalLoans   |      1,000 |      1,100 |      1,200 |
ActiveLoans  |        900 |        800 |        700 |
TotalBalance |    100,000 |    100,100 |    100,200 | 

Ideally, I am looking for a dynamic pivot, but a static query would do as well and I can try a dynamic query out of that.

Answer 1

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

Output:

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

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.

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

Answer 2

This is Giorgos Betsos solution as dynamic SQL. This will deal without the need to write the date values explicitly.

Please: If you like this: Do not mark this solution as accepted, set the acceptance to Giorgos Betsos. There's the hard work! But you may vote on it :-)

CREATE TABLE #tbl(Portfolio VARCHAR(10),[Date] DATE,TotalLoans DECIMAL(10,4),ActiveLoans DECIMAL(10,4),TotalBalance DECIMAL(10,4));
INSERT INTO #tbl VALUES
 ('P1','20151231',1000,900,100000.00)
,('P1','20151130',1100,800,100100.00)
,('P1','20151031',1200,700,100200.00);

DECLARE @pvtColumns VARCHAR(MAX)=
(
    STUFF(
    (
        SELECT DISTINCT ',['+CONVERT(VARCHAR(10), [Date] ,126) + ']'
        FROM #tbl
        FOR XML PATH('')
    )
    ,1,1,'')
);

DECLARE @cmd VARCHAR(MAX)=
'SELECT Portfolio, ColType, ' + @pvtColumns + 
' FROM (
   SELECT Portfolio, [Date], Val, ColType
   FROM (SELECT Portfolio, 
                [Date], 
                TotalLoans, 
                CAST(ActiveLoans AS DECIMAL(10,4)) AS ActiveLoans, 
                TotalBalance 
         FROM #tbl AS mytable
         WHERE Portfolio = ''P1'') AS srcUnpivot
         UNPIVOT (
            Val FOR ColType IN (TotalLoans, ActiveLoans, TotalBalance)) AS unpvt
) AS srcPivot
PIVOT (
   MAX(Val) FOR [Date] IN (' +  @pvtColumns + ')) AS pvt';

EXEC (@cmd);