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

Answer 2

You can use aggregate function sum (or max as per your needs) to achieve this.

select
    dfips,
    dcounty,
    sum(case when context = 'mail' then sumton end) mail_sumton,
    sum(case when context = 'Rail' then sumton end) rail_sumton
from your_table
group by
    dfips,
    dcounty

Answer 3

Alternatively, you can use the PIVOT function within query -- see docs at https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Answer 4

Conditional aggregation

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