I was asked about providing some results in a format that would suit a particular custom grid control. A Report Viewer control could achieve very similar functionality with an appropriate report, but I think there were other factors involved. The control allowed a form of drill-down, but required a field to be provided giving the amount of indentation. The idea being that if the indent amount on a particular row was 2, then this could be expanded to reveal all the rows that had an indent of 3. The person asking wanted a query that would provide all the information in one hit, including aggregated data at appropriate levels, and a field indicating the indent amount.
The ROLLUP operator went half way to achieve this. Using ROLLUP, you get additional rows in your resultset providing aggregated values. The example in SQL Books Online demonstrates this nicely.
But what many people miss is the GROUPING function. This is a function which takes a column and returns either 0 or 1, according to whether the row being returned is aggregating across that column or not. The example of ROLLUP above demonstrates it being used in a CASE statement.
Even fewer people realise that the GROUPING function actually returns a tinyint type, rather a bit type, and you can add tinyints easily. This gives a big clue to my indentation column. By adding the GROUPING functions together like this:
GROUPING(col1) + GROUPING(col2) + GROUPING(col3)
–you can tell how summarised the data is. Subtract this from a number like 3 (for 3 grouping columns), and it becomes the amount of indentation. Then in the ORDER BY clause, use the GROUPING functions again, and you can have the aggregate row, followed by the detail rows. Your query and results then look something like (altering the example in SQL Books Online):
SELECT CASE WHEN (GROUPING(Item) = 1) THEN ‘ALL’
ELSE ISNULL(Item, ‘UNKNOWN’)
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN ‘ALL’
ELSE ISNULL(Color, ‘UNKNOWN’)
END AS Color,
SUM(Quantity) AS QtySum,
2 – (GROUPING(Item) + GROUPING(Color)) as Indt
FROM Inventory
GROUP BY Item, Color WITH ROLLUP
ORDER BY GROUPING(Item) DESC, Item ASC, GROUPING(Color) DESC, Color ASC
Item Color QtySum Indt
——————– ——————– ————————– ——–
ALL ALL 658.00 0
Chair ALL 311.00 1
Chair Blue 101.00 2
Chair Red 210.00 2
Table ALL 347.00 1
Table Blue 124.00 2
Table Red 223.00 2
–And this happened to suit the control perfectly! Simple I know – but easy to overlook.
Other uses could easily be to use the SPACE() function with your Indt column, to provide a number of spaces for a flat text file.
