Aggregate in SQL Server-CodePudding

I need help to create new columns to calculate total of each employee salary and bonus

id          Name        PaymentType   Payment   
----        -----       ------        -------
1           John         Salary       100
2           Peter        Salary       100
3           John         Bonus        20
4           Russel       Salary       100
5           Bill         Salary       100
6           Bill         Bonus        40
7           John         Salary       100

How can I make something like below

Name          Salary     Bonus
-------------------------------
John          200        20
Peter         100        0
Russel        100        0
Bill          100        40

CodePudding user response：

We can use conditional aggregation here:

SELECT
    Name,
    SUM(CASE WHEN PaymentType = 'Salary' THEN Payment ELSE 0 END) AS Salary,
    SUM(CASE WHEN PaymentType = 'Bonus'  THEN Payment ELSE 0 END) AS Bonus
FROM yourTable
GROUP BY Name;