pivot的用法

每个城市中属于每种类别的消费者的数量
select city,no_orders,upto_two_orders,more_than_two_orders from
(
 select c.customerid,city,
  case
     when count(orderid) = 0 then 'no_orders'
     when count(orderid) <=2 then 'upto_two_orders'
     when count(orderid) >2  then 'more_than_two_orders'
  end as category
 from customers as c
 left outer join orders as o on c.customerid = o.customerid
 group by c.customerid,city
) as D
pivot
(
 count(customerid) for category in ([no_orders],[upto_two_orders],[more_than_two_orders])
) as P

---统计五年中所有人的借书情况（类似于翻转）可进一步思考，比如我想查十年的。。。
select a.employeename,[2008],[2009],[2010],[2011],[2012] from (
 select Borrower,count(borrowbookid) borrowbookcount,
   case
      when BorrowDate > '2012-1-1' then '2012'
      when BorrowDate > '2011-1-1' then '2011'
      when BorrowDate > '2010-1-1' then '2010'
      when BorrowDate > '2009-1-1' then '2009'
      when BorrowDate > '2008-1-1' then '2008'
   else '2008年以前' end as borrowYear
 from BorrowBookRecords
 group by Borrower,BorrowDate
 ) as d
 pivot
 (
  sum(borrowbookcount) for d.borrowYear in ([2008],[2009],[2010],[2011],[2012])
 ) as g
left join employees a on g.Borrower = a.employeeid