sql Pivot und berechnete Spalte

Ich versuche, eine Abfrage zusammenzustellen, die mir monatlich Einnahmen nach Kunden sowie einen SUM (Umsatz) aller Monate in der letzten Spalte zeigt. Bisher habe ich diese Abfrage unten. Das ist so nah wie ich konnte, was ich suche:

SELECT cust_num as Customer, CONVERT(CHAR(4), date, 100) + CONVERT(CHAR(4), date, 120) AS 'Month', SUM(q1.amount) AS Revenue FROM (SELECT cust_num, inv_date AS date, SUM(amount) AS amount FROM artran_mst WHERE (type = 'i') and cust_num = 'HON0994' and (inv_date BETWEEN '1/1/2017' AND '2/23/2017') GROUP BY cust_num, inv_date UNION ALL SELECT cust_num, inv_date AS date, SUM(amount*-1) AS amount FROM artran_mst WHERE (TYPE = 'c') and cust_num = 'HON0994' AND (inv_date BETWEEN '1/1/2017' AND '2/23/2017') GROUP BY cust_num, inv_date UNION ALL SELECT cust_num, inv_date AS date, SUM(amount) AS amount FROM artran_mst WHERE (TYPE = 'd') and cust_num = 'HON0994' AND (inv_date BETWEEN '1/1/2017' AND '2/23/2017') GROUP BY cust_num, inv_date) q1 GROUP BY cust_num, MONTH(date), CONVERT(CHAR(4), Date, 100) + CONVERT(CHAR(4), Date, 120) ORDER BY RIGHT(CONVERT(CHAR(4), date, 100) + CONVERT(CHAR(4), date, 120),4), MONTH(date) 

Es gibt folgendes zurück:

 +----------+----------+---------+ | Customer | Month | Revenue | +----------+----------+---------+ | HON0994 | Jan 2017 | 1170662 | | HON0994 | Feb 2017 | 787066 | +----------+----------+---------+ 

Ich muss irgendwie einen Pivot und eine berechnete Spalte hinzufügen, um die Monate als Spalten anzuzeigen, wobei die letzte Spalte eine 'totale' ist. Sehen:

 +----------+----------+----------+---------+ | Customer | Jan 2017 | Feb 2017 | Total | +----------+----------+----------+---------+ | HON0994 | 1170662 | 787066 | 1957728 | +----------+----------+----------+---------+ 

Ich denke, du willst nur bedingte Aggregation:

 SELECT cust_num, (CASE WHEN inv_date >= '2017-01-01' and inv_date < '2017-02-01' THEN (case when type = 'c' then - amount else amount end) END) as jan_2017, (CASE WHEN inv_date >= '2017-02-01' and inv_date < '2017-03-01' THEN (case when type = 'c' then - amount else amount end) END) as feb_2017, SUM(case when type = 'c' then - amount else amount end) as total FROM artran_mst WHERE (type = 'i') and cust_num = 'HON0994' and (inv_date >= '2017-01-01' and inv_date < '2017-03-01') GROUP BY cust_num;