Many rows to one row result

Question

I hope I'm explaining this well. I'm struggling with this query:

I have this table that is something like this:

InvoiceNum
Amount
Type  - where type could be item, shipping or tax. 

So what I want returned is one row per invoice: InvoiceNum, ItemAmount, ShippingAmount, TaxAmount.

Here's an example:

Invoicenum Amount Type
1          $32    Item
1          $2     Shipping
1          $1     Tax 

I would want returned:

InvoiceNum ItemAmount ShippingAmount TaxAmount 
1          $32        $2             $1

Answer 1

You can summarize rows with group by, and you can select specific rows using case:

select  InvoiceNum
,       sum(case when Type = 'Item' then Amount end) as ItemAmount
,       sum(case when Type = 'Shipping' then Amount end) as ShippingAmount
,       sum(case when Type = 'Tax' then Amount end) as TaxAmount
from    YourTable
group by
        InvoiceNum

The case statement returns null by default, and sum ignores nulls.

Answer 2

You can do this with group by and sum tricks (max works too) as @Andomar shows.

Alternatively, Microsoft SQL Server supports syntax for a PIVOT operation that helps a bit in this type of query. You still have to hard-code the column names though.

SELECT InvoiceNum, [Item] AS ItemAmount, [Shipping] AS ShippingAmount, [Tax] AS TaxAmount
FROM 
(SELECT InvoiceNum, Amount, Type FROM InvoiceTable ) i
PIVOT
(
  MAX(Amount)
  FOR Type IN ([Item], [Shipping], [Tax])
) AS pvt;