TSQL rollup -return not null

Question

I use the Rollup clause and you show how it shows aggregation at different levels, WITH NULLvalues ​​showing different levels rollups, for example. rollup(year,month,week)will show subtotals at each level.

I want it to curl up and still want to see only the highest level of aggregation. therefore, I do not want to see the values null.

Any idea how I can do this?

Relations Manjot

Answer 1

What do you mean by "only the highest level of aggregation?"

You can avoid NULL by checking the column grouping, for example:

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 set to NULL.