如何在没有UNION，ROLLUP或CUBE的情况下使用PIVOT表计算总行数?

本文介绍了如何在没有UNION，ROLLUP或CUBE的情况下使用PIVOT表计算总行数?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

有人可以帮忙计算一下此PIVOT表底部的总行吗?

Can someone help out with calculating a total row at the bottom of this PIVOT table please?

select *, [Drug1] + [Drug2] + [Drug3] + [Drug4] + [Drug5] as [Total] from (Select [id], [drug], [Diagnosis] from DrugDiagnosis ) as ptp pivot (count(id) for drug in ([Drug1], [Drug2], [Drug3], [Drug4], [Drug5]) ) as PivotTable

我知道我可以使用UNION进行操作，并有一个单独的查询来计算总数，但这将使数据库的命中率翻倍.

I know I can do it with a UNION and have a separate query to calc the totals, but that will double the hit on the database.

我已经找到了使用ROLLUP和CUBE的示例，但是这些都是过时的功能，因此我不想使用它们.

I have found examples using ROLLUP and CUBE, but these are deprecated features so I don't want to use them.

还有其他想法吗，也许是组合套装?

Any other ideas, GROUPING SETS maybe?

推荐答案

您可以使用 GROUPING SETS 以获得总计行:

You can use GROUPING SETS to get the totals row:

select isnull(diagnosis, 'Total') Diagnosis, sum([Drug1]) Drug1, sum([Drug2]) Drug2, sum([Drug3]) Drug3, sum([Drug4]) Drug4, sum([Drug5]) Drug5, sum([Drug1] + [Drug2] + [Drug3] + [Drug4] + [Drug5]) as [Total] from ( Select [id], [drug], [Diagnosis] from DrugDiagnosis ) as ptp pivot ( count(id) for drug in ([Drug1], [Drug2], [Drug3], [Drug4], [Drug5]) ) as PivotTable group by grouping sets((diagnosis), ());

请参见带演示的SQL提琴