/*----------------------------------------------------------------
  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