TSQL rollup -return not null
I am using Rollup cl开发者_如何学编程ause and you how it shows aggregation at various levels WITH NULL
values showing different levels of rollups
e.g. rollup(year,month,week)
would show subtotals at each level.
I want it rolled up and yet want to see only highest elvel of aggregation.
so I dont want to see any null
values.
Any idea how can I do that?
Regards Manjot
What do you mean by "only highest level of aggragation?
You can avoid the NULLs by checking if a column in grouped, like so:
SELECT CASE WHEN Grouping(GroupID) = 1 THEN '#ALL' ELSE GroupID END AS GroupID,
CASE WHEN Grouping(SubGroupID) = 1 THEN '#ALL' ELSE SubGroupID END AS SubGroupID,
Sum(Value)
FROM Table
GROUP BY GroupID,
SubGroupID
WITH ROLLUP
It will display #ALL insteaed of NULL.
精彩评论