行变列 pivot

SELECT p.City ,
[临时] ,
[会员] ,
VIP
FROM ( SELECT c.CustomerID ,
c.City ,
CASE WHEN COUNT(o.OrderID) <= 0 THEN N'临时'
WHEN COUNT(o.OrderID) <= 20 THEN N'会员'
WHEN COUNT(o.OrderID) > 20 THEN N'VIP'
END counts
FROM Customers c
LEFT JOIN dbo.Orders o ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID ,
c.City
) AS b PIVOT( COUNT(CustomerID) FOR counts IN ( [临时], [会员], [VIP] ) ) as p

实例

SELECT * FROM SCK_tbHeadline

SELECT DicID,ArticleID,[1] AS one,[2] AS two ,[3] AS three,[4] AS four, [5] AS five 
FROM SCK_tbHeadline PIVOT (count(ID) FOR OrderID IN ([1],[2],[3],[4],[5])) AS pvt 

结果