Thanks.

DROP TABLE #Sampledata

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

INSERT INTO #Sampledata (NAME, PAYMENT_TYPE, PAYMENT)

SELECT 'SUDHIR', 'SAL', 30.3 UNION ALL

SELECT 'SUDHIR', 'ADV', 10.3 UNION ALL

SELECT 'SUDHIR', 'ALL_1', 10 UNION ALL

SELECT 'SUDHIR', 'ALL_2', 10 UNION ALL

SELECT 'SUDHIR', 'ALL_3', 10 UNION ALL

SELECT 'MADHAV', 'SAL', 34 UNION ALL

SELECT 'MADHAV', 'ALL_1', 24 UNION ALL

SELECT 'MADHAV', 'BONUS', 10

SELECT

s.NAME,

[SAL] = s.Payment-x.ADV,

[BONUS1] = (s.Payment-x.ADV) * 1.1, -- BONUS from formula

[BONUS2] = x.BONUS,-- BONUS from Target Table

[COMM] = x.[COMM+] - x.[COMM-]

FROM #Sampledata s

OUTER APPLY (

SELECT

[ADV] = SUM(CASE WHEN PAYMENT_TYPE = 'ADV' THEN PAYMENT ELSE 0 END),

[COMM+] = SUM(CASE WHEN PAYMENT_TYPE IN ('ALL_1','ALL_2') THEN PAYMENT ELSE 0 END),

[COMM-] = SUM(CASE WHEN PAYMENT_TYPE = 'ALL_3' THEN PAYMENT ELSE 0 END),

[BONUS] = SUM(CASE WHEN PAYMENT_TYPE = 'BONUS' THEN PAYMENT ELSE 0 END)

FROM #Sampledata si

WHERE si.NAME = s.NAME

AND si.PAYMENT_TYPE <> 'SAL'

) x

WHERE s.PAYMENT_TYPE = 'SAL'

ORDER BY s.NAME DESC