SQL Statement Sum with Group By

Question

I have a table with the following structure(it cannot change) with composite primary key

Key1 (composite primary key)
Key2 (composite primary key)
Key3 (composite primary key)
Code (also part of Primary Key)
Amount
Active

User enters Codes for each Key1,Key2, Key3, Code and amounts. There are 3 codes for which user makes an entry. I can easily group them together to display sum of amount of each Code, key1,key2 and key3.

SELECT     SUM(Amount) AS sum
FROM         Table
GROUP BY Key1, Key2, Key3, Code

However, I have to write a SQL to display result like this

Key1 Key2 Key3 SumoofAmountsfCode1 SumoofAmountsfCode2 SumoofAmountsfCode3

Answer 1

There are at least two ways of doing it:

SELECT 
   Key1, 
   Key2, 
   Key3, 
   sum(case when code = 1 then Amount else 0 end) SumoofAmountsfCode1,
   sum(case when code = 2 then Amount else 0 end) SumoofAmountsfCode2,
   sum(case when code = 3 then Amount else 0 end) SumoofAmountsfCode3
from Table
group by Key1, Key2, Key3

Or using PIVOT

SELECT 
   Key1, 
   Key2, 
   Key3, 
   IsNull([1],0) SumoofAmountsfCode1,
   IsNull([2],0) SumoofAmountsfCode2,
   IsNull([3],0) SumoofAmountsfCode3
from Table A
pivot (sum(amount) for code in ([1],[2],[3])) B