SQL Pivot on subset

Question

I have the following result set:

Type | Method | Amount

Type1 Cash Amount
Type1 Check Amount
Type2 Cash Amount
Type2 Check Amount
Type3 Cash Amount
Type3 Check Amount

And I want to make it look like this:

Type | Cash | Check

Type1 Amount Amount
Type2 Amount Amount
Type3 Amount Amount

How can I achieve this in T-SQL (2005 syntax ok)? I need to pivot by type (1, 2, 3...)

Answer 1

Here's an attempt at PIVOT:

select *
from YourTable
PIVOT (sum(amount) FOR Method in (Cash,Check)) as Y

Given that it's just two columns, could try with a join:

select
    type
,   cash = a.amount
,   check = b.amount
from yourtable a
full join yourtable b on a.type = b.type
where a.method = 'cash' or b.method = 'Check'

Answer 2

Or better yet:

select
  Type
, Cash  = sum(case when Method = 'Cash'  then Amount end)
, Check = sum(case when Method = 'Check' then Amount end) 
from yourtable
group by 
  Type

Add an ELSE 0 to the CASE statements if appropriate.

This form is more flexible than the PIVOT operator and doesn't require a FULL JOIN. Just straight aggregation.