T-SQL: PIVOT

CREATE TABLE TABLE2 (ORD nvarchar(10), DT datetime, CUSTOMER nvarchar(200), PRODUCT_NAME nvarchar(200), QUANTITY decimal(18,2), PRICE decimal(18,2)) INSERT INTO TABLE2 VALUES ('001','2018-07-01','Customer Apple','TABLE 40x60',50,32.55), ('002','2018-07-02','Customer Orange','TABLE 100x60',10,65.00), ('003','2018-07-02','Customer Apple','CHAIR X1',30,15), ('004','2018-07-03','Customer Orange','CHAIR X2',20,13), ('005','2018-07-03','Customer Melon','PC IBM',10,800), ('006','2018-07-04','Customer Apple','PC HP',10,720.12), ('007','2018-07-04','Customer Orange','PC DELL',8,950), ('011','2018-08-11','Customer Apple','PC DELL',1,320.55), ('012','2018-08-12','Customer Orange','TABLE 100x60',10,25.00), ('013','2018-08-12','Customer Apple','CHAIR X1',30,15), ('014','2018-08-13','Customer Orange','CHAIR X2',50,13), ('015','2018-08-17','Customer Melon','PC IBM',5,800), ('016','2018-08-20','Customer Apple','PC HP',5,720.12), ('017','2018-08-22','Customer Orange','CHAIR X1',8,50), ('021','2018-09-01','Customer Apple','PC LENOVO',3,551), ('022','2018-09-08','Customer Orange','TABLE 200x220',1,250.00), ('023','2018-09-09','Customer Apple','CHAIR X1',30,15), ('024','2018-09-10','Customer Orange','CHAIR X2',50,13), ('025','2018-09-14','Customer Melon','CHAIR X3',90,20), ('026','2018-09-19','Customer Apple','CHAIR X4',5,12.50), ('027','2018-09-28','Customer Melon','CHAIR X1',8,50) ------------------------------------------- SELECT 'Total Amount per month' as AmountPer, *, [7]+[8]+[9] as Total FROM ( select MONTH(DT) as MonthDT, QUANTITY*PRICE as AMOUNT FROM TABLE2) AS SourceTable PIVOT ( SUM(AMOUNT) FOR MonthDT in ([7],[8],[9]) ) as PivotTable SELECT 'Total Amount per customer' as AmountPer, [Customer Apple], [Customer Melon], [Customer Orange], [Customer Apple]+[Customer Melon]+[Customer Orange] as Total FROM ( select CUSTOMER, QUANTITY*PRICE as AMOUNT FROM TABLE2) AS SourceTable PIVOT ( SUM(AMOUNT) FOR CUSTOMER in ([Customer Apple],[Customer Melon],[Customer Orange]) ) as PivotTable DROP TABLE TABLE2