在具有动态列的SQL server中将行转换为列

Question

我有这个结果

95000 1000001000000

SELECT V.Visitor_name,
SUM
(
CASE  
WHEN V.CITY='Japan' THEN V.PRICE
ELSE 0.00 
END
)AS JAPAN,
SUM
(
CASE  
WHEN V.CITY='NewYork' THEN V.PRICE
ELSE 0.00 
END
)AS NewYork,
SUM
(
CASE  
WHEN V.CITY='Paris' THEN V.PRICE
ELSE 0.00 
END
)AS Paris
FROM YOUR_TABLE AS V
GROUP BY V.Visitor_name

如果不知道城市名称，请google "dynamic pivot">

Pivot的解决方案如下:

SELECT VISITOR_NAME,[Japan],[NewYork],[Paris]
FROM 
(SELECT * FROM TEST_TABLE) AS S
PIVOT
(SUM(PRICE)
FOR CITY IN ([Japan],[NewYork],[Paris])
) AS P

测试样品:,

db&lt的在小提琴