CREATE PROC [dbo].[pivotsp]
      @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'SUM',-- Aggregate function.
      @agg_col  AS NVARCHAR(MAX),-- Column to aggregate.
      @output   AS NVARCHAR(257) = N'',-- Table for results
      @debug    AS bit = 0                          -- 1 for debugging
    AS
 
    -- Example usage:
    --    exec pivotsp
    --          'select * from vsaleshistory',--          'market,marketid,family,familyid,Forecaster,Forecasterid,product,productid',--          'month',--          'sum',--          'ku',--          '##sales'
 
    -- 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,N'''');'
 
      IF @debug = 1
         PRINT @sql
 
      EXEC sp_executesql
        @stmt   = @sql,@params = N'@result AS NVARCHAR(MAX) OUTPUT',@result = @cols OUTPUT;
 
      IF @debug = 1
         PRINT @cols
 
      -- Create the PIVOT query
      IF @output = N''
          begin
            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;'
          end
      ELSE
          begin
            set @sql = 'IF  EXISTS (SELECT * FROM tempdb.sys.objects WHERE  ' +
                'name = ''' + @output + ''' AND type = N''U'') DROP TABLE tempdb.' + @output
            EXEC sp_executesql @sql;
 
            SET @sql =
                N'SELECT * INTO ' + @output                          + @newline +
                N'FROM (SELECT '
                              + @on_rows
                              + 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;'
          end
 
        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

有了这个,很容易在可变数量的列上进行转换：

EXEC pivotsp
        'SELECT TeamID,OptionGroup,OptionID AS Options FROM OptionTeam','Teamid',-- Row headers
        'optiongroup',-- item to aggregate
        'count',-- aggregation function
        'optiongroup',-- Column header
        '##temp'         -- output table name
    SELECT * FROM ##temp

结果：

Teamid   4   5
    1   2   0
    2   1   1
    3   0   1