PIVOT和UNPIVOT使用详解



参考：

http://database.51cto.com/art/201107/276189.htm

自己：

SELECT  CityName AS '城市' ,
        [0] ,
        [100] ,
        [300] ,
        [500] ,
        [1000] ,
        [5000],
  [0]+[100]+ [300]+[500]+ [1000]+ [5000] AS [总数]
FROM    ( SELECT    ID ,
                    CityName ,
                    BonusAmount
          FROM      BuyerLotteryDetailRecord
          WHERE     Status = 1
                    AND [LotteryDate] > '2015-07-05 18:00:00.000'
                    AND [LotteryDate] < '2015-07-06 18:00:00.000'
                    AND CityName <> '商洛'
        ) p  PIVOT
( COUNT(ID) FOR BonusAmount IN ( [0], [100], [300], [500], [1000], [5000] ) ) AS pvt
ORDER BY pvt.CityName;