SQL Query - Pivot loan running totals by month due

Question

UPDATED: For Microsoft SQL Server.

This seems simple but I'm banging my head against the wall to figure it out.

I've got a single table of Loan data that I want to pivot and summarize for each month the payments were due. This is similar to a "static pool analysis" with each pool of loans for rows and the months as columns. You can see the sample data using this SQL Fiddle.

I've created a 90 second screencast to better explain the pivot and summary results I need if that helps. Thanks for your help!

Wlecome to SO. Great way to explain your question. But you should also add that set of data here. Your screencast is probably temporary and when it die your question will be useless. Please add that tables data and the desired output here if you need help formating just ask. — Jorge Campos, Feb 12 '16 at 01:57

score 1 · Answer 1 · edited May 23 '17 at 12:15

This query will solve your actual scenario:

SELECT poolmonth,
       origination,
       [201512],
       [201512]+[201601] as [201601],
       [201512]+[201601]+[201602] as [201602]
  FROM (SELECT poolmonth, 
               SUM(OriginationAmt) origination,
               SUM(CASE WHEN PmtDue = 201512
                        THEN amtpaid ELSE 0 END) as [201512],
               SUM(CASE WHEN PmtDue = 201601
                        THEN amtpaid ELSE 0 END) as [201601],
               SUM(CASE WHEN PmtDue = 201602
                        THEN amtpaid ELSE 0 END) as [201602]
          FROM Loans
         GROUP BY poolmonth
       ) newloans

See it working here: http://sqlfiddle.com/#!6/47701/12

The problem with this approach is if there are more months you would have to add then manually. In order to do this automatically you will have to create a procedure to do so like the one explained on this answer: Dynamic SQL PIVOT by @bluefeet

SQL Query - Pivot loan running totals by month due

1 Answers1