CREATE TABLE #Sampledata ( PayDate smalldatetime, NAME VARCHAR(25), PAYMENT_TYPE VARCHAR(10), PAYMENT DECIMAL(5,2)

SELECT DATEADD(mm, DATEDIFF(mm, 0, PayDate) , 0) PayMonth, s.NAME, SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) - SUM(CASE WHEN PAYMENT_TYPE = 'ADV' THEN PAYMENT ELSE 0 END) [NORMAL-SAL], SUM(CASE WHEN PAYMENT_TYPE = 'ALL_1' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_2' THEN PAYMENT ELSE 0 END) - SUM(CASE WHEN PAYMENT_TYPE = 'ALL_3' THEN PAYMENT ELSE 0 END) [NORMAL-COMM], SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) *1.1 [NORMAL-BONUS], SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) [MONTH-SAL], SUM(CASE WHEN PAYMENT_TYPE = 'ALL_1' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_2' THEN PAYMENT ELSE 0 END) [MONTH-COMM], SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_1' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_2' THEN PAYMENT ELSE 0 END) [YEARLY-BONUS]FROM #Sampledata s GROUP BY DATEADD(mm, DATEDIFF(mm, 0, PayDate) , 0), s.NAME