Very interesting as I have been working on doing a similar thing now for a few days. However I needed a Grand Total Column and a Grand Total Row for reporting purposes. I also did not know what the PivotValues would be therefore this all had to be done with dynamic SQL. I too created a temporary table in order to store the data before I pivoted the data. Here is how I did it (Please excuse the smiley's as they automatically were added. where you see a smily it's just a close paren.)
--Get the Pivot Columns by querying the distinct [Month]& Year values from the query
Declare @PivotValues as VARCHAR(8000)
Declare @PivotSum as VARCHAR(8000)
Declare @PivotTotal as VARCHAR(8000)
Declare @PivotTypes as VARCHAR(8000)
Declare @MonthYear as VARCHAR(25)
Declare @EDATEMM as INT
Declare @Year as INT
--Loop over the @YearMonth CURSOR table to build our PivotField Values
DECLARE YearMonth_cursor CURSOR FOR
SELECT DISTINCT dbo.MonthMst.[Month] + '-' + CAST(Year([EDATE]) as varchar(4)), EDATEMM, Year([EDATE])
FROM dbo.MonthMst INNER JOIN
@MasterSubset AS MS ON dbo.MonthMst.cLngMonthCode = MS.EDATEMM
Order By Year([EDATE]), EDATEMM
OPEN YearMonth_cursor
FETCH NEXT FROM YearMonth_cursor
INTO @MonthYear, @EDATEMM, @Year
WHILE @@FETCH_STATUS = 0
BEGIN
--Build the PivotValues to be used in forming the Pivot Statement
If COALESCE(@PivotValues, '') = ''
SET @PivotValues = '[' + @MonthYear + ']'
ELSE
SET @PivotValues = @PivotValues + ',[' + @MonthYear + ']'
--Build the PivotTotal for use in forming the Grand Total Column
If COALESCE(@PivotTotal, '') = ''
SET @PivotTotal = 'COALESCE([' + @MonthYear + '],0)'
ELSE
SET @PivotTotal = @PivotTotal + ' + COALESCE([' + @MonthYear + '],0)'
--Build the PivotSum for use in forming the Grand Total Row
If COALESCE(@PivotSum, '') = ''
SET @PivotSum = 'SUM(COALESCE([' + @MonthYear + '],0))'
ELSE
SET @PivotSum = @PivotSum + ', SUM(COALESCE([' + @MonthYear + '],0))'
--Build the PivotTypes for use in declaring a Table Variable
If COALESCE(@PivotTypes, '') = ''
SET @PivotTypes = '[' + @MonthYear + '] INT NULL'
ELSE
SET @PivotTypes = @PivotTypes + ', [' + @MonthYear + '] INT NULL'
-- Get the next YearMonth.
FETCH NEXT FROM YearMonth_cursor
INTO @MonthYear, @EDATEMM, @Year
END
--Add the Grand Total Column to the PivotValues
--This keeps us from having to modify the Structure in order to add
--The Grand Total column
set @PivotValues = @PivotValues + ',[Grand Total]'
--We will need to SUM the Grand Total Column in order to build the last value Grand Total
--Row
set @PivotSum = @PivotSum + ', SUM([Grand Total])'
--Add the Grand Total column to our PivotTypes as this needs to be a column in our Table Variable
set @PivotTypes = @PivotTypes + ',[Grand Total] INT NULL'
CLOSE YearMonth_cursor
DEALLOCATE YearMonth_cursor
DECLARE @SQL as NVARCHAR(4000)
--Stuff our Data into a Temporary table
--to be used in the dynamic SQL below
Select [Month] + '-' + CAST([Year] as varchar(4)) AS MonthYear, GroupCol1, GroupCol2, GroupCol3, AggregatCol
INTO #PivotData
from
(Select Month, Year, GroupCol1, GroupCol2, GroupCol3, AggregatCol From DataTable) as DT
--Now here was the tricky part. Since I didn't know what my pivot columns would be I needed
--to build dynamic SQL however I couldn't figure out how to get the data to return properly. So
--I basically created a dynamic stored proc without actually creating a stored proc.
SET @SQL = 'DECLARE @PivotTable1 TABLE( GroupCol1 varchar(50) NOT NULL,
GroupCol2 varchar(20) NOT NULL,
GroupCol3 varchar(20) NOT NULL,
' + @PivotTypes + ');
INSERT INTO @PivotTable1 (GroupCol1, GroupCol2, GroupCol3, ' + @PivotValues + ' )
Select GroupCol1, GroupCol2, GroupCol3, ' + @PivotValues + '
FROM
(Select GroupCol1, GroupCol2, GroupCol3, ' + @PivotValues + '
FROM
(
Select MonthYear,GroupCol1,GroupCol2,
GroupCol3, AggregateCol
from #PivotData
) As Source
PIVOT
(
SUM(Source.AggregateCol)
FOR MONTHYEAR IN (' + @PivotValues + ')
) As PivotTable) As PT;
Update @PivotTable1 Set [Grand Total] = ' + @PivotTotal +
'; Insert Into @PivotTable1 (GroupCol1, GroupCol2, GroupCol3, ' + @PivotValues + ')
Select ''Grand Total'', '''', '''', ' + @PivotSum + ' From @PivotTable1;
Select * From @PivotTable1;'
Exec sp_executesql @sql
Drop Table #PivotData
And here are the results:
GroupCol1 GroupCol2 GroupCol3 January-2007 February-2007 Grand Total
-------------------------------------------------------------------------------------------------
GroupValue1 Group2Value1 Group3Value1 391 356 747
GroupValue2 Group2Value2 Group3Value1 356 419 775
GroupValue3 Group2Value3 Group3Value1 181 181 362
Grand Total 928 956 1884