View Single Post
  #8 (permalink)  
Old October 23rd, 2006, 10:51 PM
Jeff Moden Jeff Moden is offline
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts

I thought you understood that WITH ROLLUP was part of the GROUP BY clause... glad you figured that one out.

The other thing you asked was "To me, it really looks like a three dimensional result set. Is that even possible in SQL?" My answer would be, "only if you know how"... :D

They say that one picture is worth a thousand words... with that in mind...

Rather than working with your data (which I can't see), let's work with some data I know we both have so we can compare apples to apples. The following example uses the Northwind database that came with SQL Server 2000... you can, of course, modify the example to suit your own conditions and joins with your own data but at least we have some common ground with the Northwind database.

First, you asked "is there a reason to prefer your code to mine?" I'd have to say yes because your code is only good for one year. You've hard-coded the column names in that particular example with "MMM YYYY". You would need to change the code for every year you wanted to report on instead of just passing a single date or a single year you want to report on. Certainly, there's no real chance of a "3 dimensional" report when you hard-code such things.

Yes, you can do "3 dimensional" reporting, as you'll soon see... just add more column names to group on and add more GROUPING clauses to correctly identify the subtotals which are an element of the dimensions you ask for. Each added column to group on would be what you are calling "conditions". Keep in mind that if you don't do a CASE/GROUPING on each of those added columns, NULLs will appear in the column at group breaks caused by the ROLLUP.

In the example below, the "ShipCountry" would equate to "Owner1", "Owner2", etc. The "Year" would be "Cond1", "Cond2" etc. And notice how the "Owner" (ShipCountry) and "Condition" (Year) are programable separately and together?

One final thing.... these complex 3 dimensional reports are most easily accomplished by creating a query to give you the data you want in a vertical fashion first. That's where the "derived table" comes in in the code below. You write the query and test the hell out of it. When it's done, then you can concentrate on the creation of the crosstab report. In case you didn't know, a "derived table" is nothing more than a NON-correlated sub-query (a regular SELECT) that has parenthesis around it, has been given a table alias, and is used in the FROM clause of another query as if it's results were actually coming from a table.

If you have any questions after testing/studying the code below, post back... oh yeah... almost forgot... run the code below from Query Analyzer in the "Text" mode instead of the "Grid" mode to get the full impact of what this report looks like...

--===== Define the database to use
    USE Northwind

--===== Suppress the auto-display of rowcounts for appearance and speed

--===== Declare some local control variables
     -- These could be parameters in a stored proc

--===== Change these to vary the output
    SET @Year = 'ALL' --'ALL' returns all years or change to a 4 digit year here
    SET @Country = 'ALL' --'ALL' returns all countries or change to a valid country name here

--===== Product the SUM of freight by country, year, and month report
 SELECT ShipCountry = CASE --Changes country name to 'Grand Total' when appropriate
                        WHEN GROUPING(ShipCountry) = 1
                         AND GROUPING([Year]) = 1
                        THEN 'GrandTotal'
                        ELSE ShipCountry
        [Year] = CASE --Changes [Year] to 'SubTotal' or blank when appropriate
                        WHEN GROUPING(ShipCountry) = 0
                         AND GROUPING([Year]) = 0
                        THEN [Year]
                        WHEN GROUPING(ShipCountry) = 0
                         AND GROUPING([Year]) = 1
                        THEN 'SubTotal'
                        ELSE ' '
        ' Jan' = STR(SUM(CASE WHEN [Month] = 01 THEN Freight ELSE 0 END),8,2),
        ' Feb' = STR(SUM(CASE WHEN [Month] = 02 THEN Freight ELSE 0 END),8,2),
        ' Mar' = STR(SUM(CASE WHEN [Month] = 03 THEN Freight ELSE 0 END),8,2),
        ' Apr' = STR(SUM(CASE WHEN [Month] = 04 THEN Freight ELSE 0 END),8,2),
        ' May' = STR(SUM(CASE WHEN [Month] = 05 THEN Freight ELSE 0 END),8,2),
        ' Jun' = STR(SUM(CASE WHEN [Month] = 06 THEN Freight ELSE 0 END),8,2),
        ' Jul' = STR(SUM(CASE WHEN [Month] = 07 THEN Freight ELSE 0 END),8,2),
        ' Aug' = STR(SUM(CASE WHEN [Month] = 08 THEN Freight ELSE 0 END),8,2),
        ' Sep' = STR(SUM(CASE WHEN [Month] = 09 THEN Freight ELSE 0 END),8,2),
        ' Oct' = STR(SUM(CASE WHEN [Month] = 10 THEN Freight ELSE 0 END),8,2),
        ' Nov' = STR(SUM(CASE WHEN [Month] = 11 THEN Freight ELSE 0 END),8,2),
        ' Dec' = STR(SUM(CASE WHEN [Month] = 12 THEN Freight ELSE 0 END),8,2),
        ' Total' = STR(SUM(Freight),8,2),
        ' ' = CASE WHEN GROUPING([Year])=1 THEN CHAR(13)+CHAR(13) ELSE '' END --Makes group breaks at sub-totals
   FROM (--Derived table does all the necessary conversions and pre-totals
         SELECT ShipCountry,
                [Year] = CAST(YEAR(OrderDate) AS CHAR(8)),
                [Month] = MONTH(OrderDate),
                Freight = SUM(Freight)
           FROM dbo.Orders
          WHERE YEAR(OrderDate) = CASE
                                    WHEN @Year = 'ALL' THEN STR(YEAR(OrderDate),8)
                                    ELSE @Year
            AND ShipCountry = CASE
                                    WHEN @Country = 'ALL'
                                    THEN ShipCountry
                                    ELSE @Country
          GROUP BY ShipCountry, CAST(YEAR(OrderDate) AS CHAR(8)),MONTH(OrderDate)
        ) d --End derived table
  GROUP BY ShipCountry,[Year] WITH ROLLUP
  ORDER BY GROUPING(ShipCountry),ShipCountry,GROUPING([Year]), [Year]

--Jeff Moden
Reply With Quote
The Following User Says Thank You to Jeff Moden For This Useful Post:
moorecreative (April 11th, 2015)