sql - How to convert aggregation results into columns?

Question

Please advise me if you know the terminology to describe to following action:

Input

dfips   dcounty        context  sumton
19001   Adair County    mail    6521.79995560646
19001   Adair County    Rail    38411.5996840298

Output:

dfips   dcounty       mail_sumton      rail_sumton 
19001 Adair County    6521.79995560646 38411.5996840298

I want to convert the input to the output, but I am not sure how to describe such action. The best I can come up with is convert aggregation results into columns.

Answer 1

The simple crosstab version of a pivot() would look like this:

select 
    dfips
  , dcounty
  , mail_sumton = sum(case when context = 'mail' then sumton else null end)
  , rail_sumton = sum(case when context = 'rail' then sumton else null end)
from t
group by dfips, dcounty