tnpich (9/2/2015)
The one posted by you back in 2007. I wasn't paying attention to the dates when I was looking at this.My SQL skills are not much past the beginner level although I can usually pick up some concepts quickly. I'm finding that summarization and pivoting are things I'm having problems with.
I'll try playing with your suggestion.
Thanks,
That code has a problem that might not have been present at the time. The columns are not guaranteed to be created in the correct order when using SQL Server 2005 or a more recent version.
I did some changes in case someone would like to use the procedure. The method used to build the select list is explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
And I strongly suggest reading Jeff's articles on pivots and cross tabs found here:
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.
Rev 01 - 09/02/2015 - Luis Cazare - Changed the concatenation method for
pivoted columns. Use a single variable
**************************************************************************/
--===== 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 @MySQL VARCHAR(MAX)
SELECT @MySQL =
--===== Build the SELECT clause
CASE
WHEN @pTotals IN (2,3)
THEN 'SELECT CASE WHEN GROUPING(RNam) = 0 '
+ 'THEN CAST(RNam AS VARCHAR(128)) '
+ 'ELSE ''Total'' END AS ' + QUOTENAME(@pRowName)
ELSE 'SELECT RNam AS ' + QUOTENAME(@pRowName)
END
--===== Build the select LIST
+ (
SELECT CHAR(13) + CHAR(9)
+ ',SUM(CASE WHEN CNam=''' + CNam + ''''
+ 'THEN CVal ELSE 0 END) AS ' + QUOTENAME(CNam)
FROM #Results
GROUP BY CNam
ORDER BY CNam
FOR XML PATH(''),TYPE).value('.', 'varchar(max)')
--===== If totals are turned on, calculate the row total
+ CASE
WHEN @pTotals IN (1,3)
THEN CHAR(13) + CHAR(9) + ',SUM(CVal) AS Total'
ELSE ''
END
--===== Build the FROM and GROUP BY clauses
+ CHAR(13) + 'FROM #Results GROUP BY RNam'
-- If the totals are turned on, calculate column totals
+ 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 @MySQL
ELSE
EXEC (@MySQL)
GO