/*---------------------------------------------------------------- Demo example of pivot_sp, originally written by Itzik Ben-Gan, and adapted by Erland Sommarskog. See http://www.sommarskog.se/dynamic_sql.html#Crosstab for more details. ----------------------------------------------------------------*/ USE Northwind go ------------------ The procedure itself ------------------ CREATE PROC dbo.pivot_sp @query AS NVARCHAR(MAX), -- The query, can also be the name of a table/view. @on_rows AS NVARCHAR(MAX), -- The columns that will be regular rows. @on_cols AS NVARCHAR(MAX), -- The columns that are to be pivoted. @agg_func AS NVARCHAR(257) = N'MAX', -- Aggregate function. @agg_col AS NVARCHAR(MAX), -- Column to aggregate. @debug AS bit = 1 AS -- Input validation IF @query IS NULL OR @on_rows IS NULL OR @on_cols IS NULL OR @agg_func IS NULL OR @agg_col IS NULL BEGIN RAISERROR('Invalid input parameters.', 16, 1); RETURN; END -- Additional input validation goes here (SQL Injection attempts, etc.) BEGIN TRY DECLARE @sql AS NVARCHAR(MAX), @cols AS NVARCHAR(MAX), @newline AS NVARCHAR(2); SET @newline = NCHAR(13) + NCHAR(10); -- If input is a valid table or view -- construct a SELECT statement against it IF COALESCE(OBJECT_ID(@query, N'U'), OBJECT_ID(@query, N'V')) IS NOT NULL SET @query = N'SELECT * FROM ' + @query; -- Make the query a derived table SET @query = N'(' + @query + N') AS Query'; -- Handle * input in @agg_col IF @agg_col = N'*' SET @agg_col = N'1'; -- Construct column list SET @sql = N'SET @result = ' + @newline + N' STUFF(' + @newline + N' (SELECT N'','' + quotename( ' + 'CAST(pivot_col AS sysname)' + + ') AS [text()]' + @newline + N' FROM (SELECT DISTINCT(' + @on_cols + N') AS pivot_col' + @newline + N' FROM' + @query + N') AS DistinctCols' + @newline + N' ORDER BY pivot_col' + @newline + N' FOR XML PATH(''''))' + @newline + N' ,1, 1, N'''');' IF @debug = 1 PRINT @sql EXEC sp_executesql @stmt = @sql, @params = N'@result AS NVARCHAR(MAX) OUTPUT', @result = @cols OUTPUT; -- Create the PIVOT query SET @sql = N'SELECT *' + @newline + N'FROM (SELECT ' + @on_rows + N', ' + @on_cols + N' AS pivot_col' + N', ' + @agg_col + N' AS agg_col' + @newline + N' FROM ' + @query + N')' + + N' AS PivotInput' + @newline + N' PIVOT(' + @agg_func + N'(agg_col)' + @newline + N' FOR pivot_col IN(' + @cols + N')) AS PivotOutput;' IF @debug = 1 PRINT @sql EXEC sp_executesql @sql; END TRY BEGIN CATCH DECLARE @error_message AS NVARCHAR(2047), @error_severity AS INT, @error_state AS INT; SET @error_message = ERROR_MESSAGE(); SET @error_severity = ERROR_SEVERITY(); SET @error_state = ERROR_STATE(); RAISERROR(@error_message, @error_severity, @error_state); RETURN; END CATCH GO -- Since the procedure is wide-open to SQL injection, make sure that plain users -- cannot run it directly DENY EXECUTE ON pivot_sp TO public go ----------------------------------------------------------------------------- -- Test procedure that gives some examples on using pivot_sp. CREATE PROCEDURE test_sp AS -- Count of orders per employee and order year -- pivoted by order month EXEC pivot_sp @query = N'Orders', @on_rows = N'YEAR(OrderDate) AS OrderYear', @on_cols = N'MONTH(OrderDate)', @agg_func = N'COUNT', @agg_col = N'*', @debug = 1; -- Sum of value (quantity * unit price) per employee -- pivoted by order year EXEC pivot_sp @query = N'SELECT O.OrderID, O.EmployeeID, O.OrderDate, OD.Quantity, OD.UnitPrice FROM Orders AS O JOIN [Order Details] AS OD ON OD.OrderID = O.OrderID', @on_rows = N'EmployeeID', @on_cols = N'YEAR(OrderDate)', @agg_func = N'SUM', @agg_col = N'Quantity * UnitPrice', @debug = 1; go ----------------------------------------------------------------------- -- Create a plain user, and grant him SELECT and EXECUTE rights in the -- the database. CREATE USER frits WITHOUT LOGIN GRANT SELECT, EXECUTE TO frits go -- Run test_sp as frits. EXECUTE AS USER = 'frits' go -- This runs fine. EXEC test_sp go -- But when frits tries to run pivot_sp, this fails, because DENY takes -- precedence of GRANT. EXEC pivot_sp @query = N'Orders', @on_rows = N'YEAR(OrderDate) AS OrderYear', @on_cols = N'MONTH(OrderDate)', @agg_func = N'COUNT', @agg_col = N'*', @debug = 1; go REVERT go -- Cleanup DROP PROCEDURE dbo.pivot_sp; DROP PROCEDURE test_sp; DROP USER frits; GO