CREATE PROCEDURE dbo.AutoCrossTab
/**************************************************************************
 Purpose:
 -------
 Given data in a 3 column external temp table, "auto-magically" create a 
 pivot report for that data.

 Inputs:
 -------
 @pRowName:
    Optional - Defaults to 'Row Name' and will appear down the left side
               of the report in the first column.
 
 @pTotals
    Optional - Defaults to 0
           0 = No totals
           1 = Row totals displayed in last column on right
           2 = Column totals displayed in last row at bottom
           3 = Both sets of totals displayed

 @pDebug
    Optional - Defaults to 0
           0 = Pivot report will be output
           1 = SQL that creates the pivot report will be output 

 Outputs:
 --------
 Pivot report
    Left Column - Created from RNam column of the existing #Results table.
   Column Names - Totals will be named 'Total' for rows and columns.
                - Left most column name defaults to 'Row Name' or may be
                  assigned by the @pRowName parameter.
                - All other column names dervied from CNam column of the 
                  existing #Results table.
        Content - Sum aggragated CVal column from the existing #Results 
                  table.

 Revision History:
 Rev 00 -  Date Unk  - Jeff Moden - Initial creation
 Rev 01 - 10/20/2007 - Jeff Moden - Converted to stored procedure and
                                    extra functionality added through
                                    parameters explained above.
**************************************************************************/
--===== Declare the I/0 parameters   
        @pRowName VARCHAR(128) = 'Row Name',
        @pTotals  INTEGER      = 0,
        @pDebug   INTEGER      = 0
     AS
     
--===== Setup the environment
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

--===== Declare some local variables to hold some Dynamic SQL
DECLARE @MySQL1 VARCHAR(8000)  --SELECT and Row Name
DECLARE @MySQL2 VARCHAR(8000)  --Select LIST
DECLARE @MySQL3 VARCHAR(8000)  --Optional Row Totals
DECLARE @MySQL4 VARCHAR(8000)  --FROM, GROUP BY, and optional column totals

--===== Build the SELECT clause
    SET @MySQL1 = 
            CASE
            WHEN @pTotals IN (2,3)
            THEN 'SELECT CASE WHEN GROUPING(RNam) = 0 '
               + 'THEN CAST(RNam AS VARCHAR(128)) '
               + 'ELSE ''Total'' END [' + @pRowName + '],' + CHAR(13) 
            ELSE 'SELECT RNam[' + @pRowName + '],' + CHAR(13) 
            END

--===== Build the select LIST 
 SELECT @MySQL2 = 
            ISNULL(@MySQL2 + ','+CHAR(13),'') + 
          + 'SUM(CASE WHEN CNam=''' + CNam + '''' 
          + 'THEN CVal ELSE 0 END)' + '[' + CNam + ']'
   FROM (--==== Derived table forces order of column names
         SELECT DISTINCT TOP 100 PERCENT CNam AS CNam
           FROM #Results
          ORDER BY CNam) d

--===== If totals are turned on, calculate the row total
 SELECT @MySQL3 = 
            CASE 
            WHEN @pTotals IN (1,3)
            THEN CHAR(13) + ',SUM(CVal) AS Total' 
            ELSE '' 
            END

--===== Build the FROM and GROUP BY clauses
     -- If the totals are turned on, calculate column totals
 SELECT @MySQL4 = 
            CHAR(13) + 'FROM #Results GROUP BY RNam'
          + CASE 
            WHEN @pTotals IN (2,3) 
            THEN ' WITH ROLLUP' 
            ELSE '' 
            END

--===== If debug mode is on, just print the dynamic SQL... 
     -- Otherwise, execute the dynamic SQL
     IF @pDebug = 1 
        PRINT @MySQL1+@MySQL2+@MySQL3+@MySql4
   ELSE EXEC (@MySQL1+@MySQL2+@MySQL3+@MySql4)
GO

--===== Setup the environment
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

--===== If the temp table to hold the #Results in exists, drop it
     IF OBJECT_ID('TempDB..#Results') IS NOT NULL 
        DROP TABLE #Results

--===== Populate the temp table with #Results from your original query... for example...
 SELECT [field to be rows]            AS RNam, --<<< Column alias must be this
        [field to be columns]         AS CNam, --<<< Column alias must be this
        SUM([field to be aggregated]) AS CVal  --<<< Column alias must be this
   INTO #Results  --<<< Table name must be this!!!
   FROM [your table/view]
  GROUP BY [field to be rows], [field to be columns]

--===== Create an index for a little extra speed
 CREATE CLUSTERED INDEX IDX_#Results_RNam ON #Results (RNam)

--===== Create either the Pivot table report or...
     -- build the SQL that does so it can be "customized".
     -- Parameters are for example... read the header of the proc for details
   EXEC dbo.AutoCrossTab @pRowName = '2 Letter Account',
                         @pTotals  = 3,  --Print row and column totals
                         @pDebug   = 0   --Display the report, not the dynamic SQL