我尝试了旋转SQL表，但无法获得所需的结果。
我的桌子看起来像这样
DOEntry RouteID StoreID Status
----------------------------------------------------
01-02-2016 100 1134 Complete
01-02-2016 100 419 No Visit
01-02-2016 100 420 Complete
01-02-2016 100 423 Complete
01-02-2016 100 451 Complete
01-02-2016 100 457 Complete
01-02-2016 100 488 Complete
01-02-2016 100 667 Complete
01-02-2016 101 373 Complete
01-02-2016 101 374 Complete
01-02-2016 101 376 Complete
01-02-2016 101 427 Complete
01-02-2016 101 460 Complete
01-02-2016 101 465 Complete
01-02-2016 101 616 No Visit
01-02-2016 101 688 No Visit
02-02-2016 100 421 No Visit
02-02-2016 100 422 No Visit
02-02-2016 100 424 Complete
02-02-2016 100 425 No Visit
02-02-2016 100 456 No Visit
02-02-2016 100 458 Complete
02-02-2016 100 459 Complete
02-02-2016 100 486 Complete
02-02-2016 101 375 Complete
02-02-2016 101 377 Complete
02-02-2016 101 378 Complete
02-02-2016 101 383 No Visit
02-02-2016 101 426 Complete
02-02-2016 101 604 Complete
02-02-2016 101 605 No Visit
02-02-2016 101 609 Complete
我如何需要枢轴看
DOEntry RouteID [Complete] [InComplete] [No Visit]
01-02-2016 100 7 0 1
01-02-2016 101 6 0 2
02-02-2016 100 4 0 4
02-02-2016 101 6 0 2
Complete，[InComplete]，[No Visit]是在StoreID中找到的总和仅供参考：在给定的情况下，我确实了解到您没有看到
InComplete，但确实存在InComplete。谢谢
最佳答案
考虑到STATUS列中的值的数量是固定的并且是已知的。如果值不是固定的并且在状态中未知，则需要使用Dynamic Sql进行透视
使用Conditional Aggregate执行此操作
SELECT DOEntry,
RouteID,
[Complete]= Sum(Iif(status = 'Complete', 1, 0)),
[InComplete]= Sum(Iif(status = 'InComplete', 1, 0)),
[No Visit]= Sum(Iif(status = 'No Visit', 1, 0))
FROM Yourtable
GROUP BY DOEntry,
RouteID
Order by DOEntry
或者您可以使用
PIVOT运算符SELECT DOEntry,
RouteID,
[Complete],
[InComplete],
[No Visit]
FROM Yourtable
PIVOT (Count(StoreID)
FOR Status IN ([Complete],
[InComplete],
[No Visit]))pv
ORDER BY DOEntry
结果：
╔════════════╦═════════╦══════════╦════════════╦══════════╗
║ DOEntry ║ RouteID ║ Complete ║ InComplete ║ No Visit ║
╠════════════╬═════════╬══════════╬════════════╬══════════╣
║ 2016-01-02 ║ 100 ║ 7 ║ 0 ║ 1 ║
║ 2016-01-02 ║ 101 ║ 6 ║ 0 ║ 2 ║
║ 2016-02-02 ║ 100 ║ 4 ║ 0 ║ 4 ║
║ 2016-02-02 ║ 101 ║ 6 ║ 0 ║ 2 ║
╚════════════╩═════════╩══════════╩════════════╩══════════╝
两者都有类似的执行计划，您可以使用它们中的任何一个
条件性骨料
枢