How to pivot the below table based on case statement
example if any id has a type in(5,6,7,8) and code in (awe,btw,gtw,uyt) i would like the amount1 to be reflected under a new column acc_amount
if the id has a type in (7,8) and a code in (gtw,uyt) then i would like the amount1 in a new colum sec_amount
if type in (1) and code = gts then transpose amount2 column to a a new column mec_amount
if type = 3 and code = nbn in amount2 column transpose to mec_amount
I alo have 7 other coulms than need to be created from the values in the below table, using the same example as above
CXURRENT TABLE
id,amount1, amount2,type,code
2, 50 ,87, 5,awe
2,50, 88,6,btw
2,80,98,1,gts
3,80,98,7,gtw
3,80,97,8,uyt
4,60, 45,3,nhg
4,60,45,2,mutr
2,47,66,3,nbn
PROJECTED TABLE
id, acc_amount, sec_amount,mec_amount
2 , 100, 175,98,66
3,160,195
4,120,90