pivot and unpivot

;with a (region,product,amount)
as
(
   select 'bejing','car',3
   union
   select 'bejing','bike',33
   union
   select 'shanghai','car',31
   union
   select 'shanghai','bike',331
)

/* old way
select 
region,
bike = max(case when product = 'bike' then amount end),
car = MAX(case when product = 'car' then amount end)
from a
group by region
*/

-- using pivot
select 
  region,
  bike,
  car
from a pivot
(
  max(amount)
  for product in(bike,car)
)as pv

;with b (region,bike, car)
as
(
   select 'beijing', 33,3
   union
   select 'shanghai',331,31
)

-- using unpivot
select region,unpvt.product, unpvt.amount
 from b unpivot
 (
   amount for product in(bike,car)
 ) as unpvt