How To Convert Aggregation Results Into Columns?

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

Solution 1:

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

select 
    dfips
  , dcounty
  , mail_sumton =sum(casewhen context ='mail'then sumton elsenullend)
  , rail_sumton =sum(casewhen context ='rail'then sumton elsenullend)
from t
groupby dfips, dcounty

Solution 2:

Conditional aggregation

select 
   dfips,
   dcounty,
   sum(casewhen context ='mail'then isnull(sumton,0) elsenullend) as mail_sumton,
   sum(casewhen context ='rail'then isnull(sumton,0) elsenullend) as rail_sumton,
from yourTable
groupby
   dfips, dcounty

Solution 3:

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

select
    dfips,
    dcounty,
    sum(casewhen context ='mail'then sumton end) mail_sumton,
    sum(casewhen context ='Rail'then sumton end) rail_sumton
from your_table
groupby
    dfips,
    dcounty

Solution 4:

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