参考:
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;