小爱，帮我看看这存储过程，调好了，分都给你....

IF OBJECT_ID('USP_PIVOT') IS NOT NULL DROP PROCEDURE USP_PIVOT

GO

---SELECT * FROM TB PIVOT(MAX(COL1) FOR COL2 IN( , , , , , , ))

CREATE PROCEDURE USP_PIVOT

    @schema_name AS SYSNAME =N'dbo'   --架构名称

   ,@object_name AS SYSNAME =NULL     --表/视图名称

   ,@on_row  AS SYSNAME=NULL          --分组列

   ,@on_cols AS SYSNAME=NULL          --旋转列

   ,@agg_func AS NVARCHAR(10)=N'MAX'  --聚合函数

   ,@agg_col AS SYSNAME=NULL          --统计列

AS

BEGIN

     DECLARE @object AS NVARCHAR(600)

            ,@sql AS NVARCHAR(MAX)

            ,@cols AS NVARCHAR(MAX)

            ,@newline AS NVARCHAR(2)

            ,@msg AS NVARCHAR(500);

      

     --检查是否缺少输入

     IF @schema_name IS NULL

        OR @object_name IS NULL

        OR @on_row IS NULL

        OR @on_cols IS NULL

        OR @agg_func IS NULL

        OR @agg_col IS NULL

     BEGIN

          SET @msg=N'Missing input parameters:'

          +CASE WHEN @schema_name IS NULL THEN N'@schema_name' ELSE '' END

          +CASE WHEN @object_name IS NULL THEN N'@object_name' ELSE '' END

          +CASE WHEN @on_row IS NULL THEN N'@on_row' ELSE '' END

          +CASE WHEN @on_cols IS NULL THEN N'@on_cols' ELSE '' END

          +CASE WHEN @agg_func IS NULL THEN N'@agg_func' ELSE '' END

          +CASE WHEN @agg_col IS NULL THEN N'@agg_col' ELSE '' END

          +'';      

          RAISERROR(@msg,16,1);

          RETURN @msg;

     END

            

     SET @newline=NCHAR(13)+NCHAR(10);

     SET @object=QUOTENAME(@schema_name)+'.'+QUOTENAME(@object_name);

     

     --只允许在已存在的表或视图作为输入对象

     IF COALESCE(OBJECT_ID(@object,N'U'),OBJECT_ID(@object,N'V')) IS NULL

     BEGIN

          SET @msg=N'%s is not a existing table or view in the database.';

          RAISERROR(@msg,16,1,@object);

          RETURN @msg;

     END

     

     --检查@on_row,@on_cols,@agg_col列名称是否存在

     IF COLUMNPROPERTY(OBJECT_ID(@object),@on_row,'ColumnId') IS NULL OR

        COLUMNPROPERTY(OBJECT_ID(@object),@on_cols,'ColumnId') IS NULL OR

        COLUMNPROPERTY(OBJECT_ID(@object),@agg_col,'ColumnId') IS NULL

     BEGIN

          SET @msg=N'%s,%s and %S must a existing column in %s';

          RAISERROR(@msg,16,1,@on_row,@on_cols,@agg_col,@object);

          RETURN @msg;

     END

     

     --检查@agg_func是否是已存在聚合函数

     IF @agg_func NOT IN(N'AVG',N'MAX',N'SUM',N'COUNT',N'COUNT_BIG',N'MIN',N'STDEV',N'STDEV',N'VAR',N'VARP')

     BEGIN

          SET @msg=N'%s is an unsupported aggregate function.';

          RAISERROR(@msg,16,1,@agg_func);

          RETURN @msg;

     END

     

     --构造列列表 

     --STUFF((SELECT '',''+on_cols FROM (SELECT DISTINCT '+QUOTENAME(@on_cols)+' AS [on_cols] FROM '+@object+') P FOR XML PATH(''''))),1,1,,'''')

     SET @sql=N'SET @RESULT='                        +@newline+

              N'  STUFF('                            +@newline+

              N'  (SELECT N'',''+'                    

                         +N'QUOTENAME(on_cols) AS [TEXT()]'  +@newline+

              N'          FROM (SELECT DISTINCT ('            

                          +QUOTENAME(@on_cols)+N' AS [on_cols]' +@newline+

              N'          FROM '+@object+N') AS P'              +@newline+

              N'          ORDER BY on_cols'                     +@newline+

              N'          FOR XML PATH('''')),'                  +@newline+

              N'          1,1,N'''');'

      EXEC sp_executesql @sql,N'@RESULT AS NVARCHAR(MAX) OUTPUT',@RESULT=@cols OUTPUT;

      PRINT @cols

      

      --创建PIVOT查询

      SET @sql=N'SELECT *'                                  +@newline+

               N'FROM '                                     +@newline+

               N'(SELECT '                                  +@newline+

               N'    '+QUOTENAME(@on_row)+N','              +@newline+

               N'    '+QUOTENAME(@on_cols)+N' AS pivot_col,'+@newline+

               N'    '+QUOTENAME(@agg_col)+N' AS agg_col'   +@newline+

               N'    FROM '+@object                         +@newline+

               N') AS PivotInput'                           +@newline+

               N'    PIVOT'                                 +@newline+

               N'    ('+@agg_func+N'(agg_col)'              +@newline+

               N'     FOR pivot_col'                        +@newline+

               N'      IN (' +@cols+ N')'                     +@newline+

               N'    ) AS P;';

      EXEC @sql    

END

GO