benjamin.nelson06 (5/13/2008)

---

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