Search code examples
sql-server-2008sql-server-2005rollup

Showing rollup in last line only

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.

Solution

  • 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]';