Showing rollup in last line only

Question

This is needed to work in both SQL Server 2005 and 2008 without having to adjust the compatibility level (if it can be done)

Select 
    CASE GROUPING([Store ID]) WHEN 1 THEN '[Store ID]' ELSE [Store ID] END [Store ID],
    CASE GROUPING([Cashier]) WHEN 1 THEN '[Cashier]' ELSE [Cashier] END [Cashier],
    CASE GROUPING([Register]) WHEN 1 THEN '[Register]' ELSE [Register] END [Register],
    sum([Amex]) AS [Amex],
    sum([C.Card]) AS [C.Card],
    sum([Cash]) AS [Cash],
    sum([House Acct]) AS [House Acct],
    sum([MasterCard]) AS [MasterCard],
    sum([Str Cr]) AS [Str Cr],
    sum([Visa/MC]) AS [Visa/MC] 
from 
    #a13 
group by 
    [Store ID],
    [Cashier],
    [Register] 
with rollup

This returns : enter image description here

How could I adjust the code not to actually show the rolled up rows? Meaning remove the last 3 lines or where it has anything with [ ]. I need the Rollup to use in SQL 2005 so I can use the case grouping.

I don't know how to go about it. Can't use `GROUPING SETS(())` because it is not supported by 2005 (or needs to adjust the compatibility to 90 which I can't do, forgot the actual reason for why I can't use it). I could remove the `WITH ROLLUP` and it would work in 2008 but 2005 needs it there. — JohnZ, Feb 28 '13 at 22:07

score 1 · Accepted Answer · answered Feb 28 '13 at 22:46

1

Now that I understand the problem a little better, my first suggestion is to make your presentation layer a little smarter than just having to barf whatever the database sends to it.

However I'm not sure why you can't do something like this, instead of the mess you described with a temp table:

SELECT * FROM 
(
  ...your query above...
) AS x
WHERE Register <> '[Register]';

answered Feb 28 '13 at 22:46

Aaron Bertrand

272,866
37
466
490

This worked fine. I can't do anything about the presentation layer as I am not the one in charge of it or do anything with it. I am just in charge of getting the data that is to be displayed. – JohnZ Feb 28 '13 at 22:56

score 0 · Answer 2 · answered Feb 28 '13 at 22:14

0

Nevermind, I just put these results into temp table, removed those lines and then just select from the temp table.

answered Feb 28 '13 at 22:14

JohnZ

382
2
8
20

1

This seems like a grossly inefficient solution. Since your presentation tier has to loop through all of the rows to display the data anyway, doesn't it make sense for that code to decide when to display the individual values? – Aaron Bertrand Feb 28 '13 at 22:33
This code goes to the server it all get processed there and the presentation (website) only displays my final select query. How else could I go about this within the provided code? – JohnZ Feb 28 '13 at 22:35
What does "only displays my final select query" mean? It must loop through the results in order to display them, right? – Aaron Bertrand Feb 28 '13 at 22:37
The website only sees and handles `select * from #temp` result which only has the 1 row with out the rows that have [ ] in them. All the handling and such is done on the SQL server. – JohnZ Feb 28 '13 at 22:40

score 0 · Answer 3 · answered Mar 01 '13 at 11:57

why not use a CTE instead? If you don't want all the rows produced by the rollup, return to a basic group by - but do it within a CTE, e.g. with CTE as ( <> ) select * from cte union all select sum(of each field) from CTE -- i.e. the grand total

you may have to introduce a constant in the CTE output (e.g. 1) and output 2 in the final row to ensure it orders properly.

Showing rollup in last line only

3 Answers3