Sql Server: Merge Two Rows Of Data From Same Table And Save Into Another Table

Is there a way to merge two rows of data and save into another table in SQL Server? Example, I have Table A (Month, Category, AmountBought) with 4 rows of data : - January, Student

Solution 1:

You can use aggregation to do the pivoting:

select month,
    sum(case when category = 'Student' then AmountBought else 0 end) as AmountOfStudentBought,
    sum(case when category = 'Lecturer' then AmountBought else 0 end) as AmountOfLecturerBought
from your_table
group by month;