如何在SQL select语句中动态创建列

您将需要一个动态的枢纽来做到这一点。这是存储过程：

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, 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', ' + @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        IF @debug = 1PRINT @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