Dynamic Pivot table wizard SQL Server

原文 http://www.gyurcit.hu/pivot.html

Dynamic Pivot table wizard

This stored procedure generate dynamic crosstable with multiple pivot columns by 4+1 parameters.

1. P_Row_Field	Name of field that reperesent the spreadsheet's rows
2. P_Column_Field	Name of field that store the name of columns
3. P_Value	Name of field that represent the spreedsheet's value
4. P_From	Name of source table
5. P_Where	Filter expression (optional)

Values of second parameter field should not include apastrophe.  Procedure does not check paramaters (you must do that), so it may causes sql injection attack.

To source code click one of them

• MS-SQL
• MySQL

-- =================================================
-- Pivot Table Wizard            (c) 2009 by GyurcIT
-- http://www.gyurcit.hu   e-mail: [e-mail]
-- =================================================

IF EXISTS (SELECT name FROM sysobjects
           WHERE  name = N'PivotWizard' AND type = 'P')
    DROP PROCEDURE PivotWizard
GO
-----------------------------------------------------
-----------------------------------------------------
CREATE PROCEDURE PivotWizard
   @P_Row_Field    VARCHAR(255),
   @P_Column_Field VARCHAR(255),
   @P_Value        VARCHAR(255),
   @P_From         VARCHAR(4000),
   @P_Where        VARCHAR(4000) = '1=1'
AS

  DECLARE @SQL NVARCHAR(4000)

  -- Build SQL statment that upload @Columns string 

  -- with @P_Column_Filed values
  CREATE TABLE #TEMP  (ColumnField varchar(100))
  SET @sql ='SELECT DISTINCT '+@P_Column_Field+' AS ColumnField'+
              ' FROM '+@P_From+
              ' WHERE '+@P_Where+
              ' ORDER BY '+@P_Column_Field
  INSERT INTO #TEMP
  EXEC(@sql)
  PRINT @sql

  -- Check count of columns
  DECLARE @Count_Columns int
  SELECT @Count_Columns = COUNT(*) FROM #Temp
  IF (@Count_Columns<1) OR (@Count_Columns>255)  BEGIN
      DROP TABLE #Temp
      RAISERROR('%d is invalid columns amount. Valid is 1-255',

16,1,@Count_columns)
      RETURN
  END
  -- Upload @Columns from #Temp
  DECLARE @Columns VARCHAR(8000),
          @Column_Field VARCHAR(8000)

  SET @Columns = ''
  DECLARE Column_cursor CURSOR LOCAL FOR
  SELECT CAST(ColumnField AS VARCHAR(60))
  FROM #Temp
  OPEN Column_cursor
  FETCH NEXT FROM Column_cursor
  INTO @Column_Field
  WHILE @@FETCH_STATUS = 0 BEGIN
      SET @Columns = @Columns +
        ' SUM('+
             ' CASE WHEN '+@P_Column_Field+'='''+ @Column_Field+''''+
             ' THEN '+@P_Value+
             ' ELSE 0 END'+
             ') AS ['+ @Column_Field +'], '
      FETCH NEXT FROM Column_cursor
      INTO @Column_Field
  END
  CLOSE Column_cursor
  DEALLOCATE Column_cursor
  DROP TABLE #Temp

  IF @Columns='' RETURN 1
  SET @Columns = Left(@Columns,Len(@Columns)-1)

  -- Build Pivot SQL statment
  DECLARE @Pivot_SQL VARCHAR(8000)
  SET @Pivot_SQL =              'SELECT '  +@P_Row_Field+', '+@Columns
  SET @Pivot_SQL = @Pivot_SQL +' FROM '    +@P_From
  SET @Pivot_SQL = @Pivot_SQL +' WHERE '   +@P_Where
  SET @Pivot_SQL = @Pivot_SQL +' GROUP BY '+@P_Row_Field
  SET @Pivot_SQL = @Pivot_SQL +' ORDER BY '+@P_Row_Field
  SET @Pivot_SQL = @Pivot_SQL + '#'

  IF Right(@Pivot_SQL,1)<>'#'
  BEGIN
     RAISERROR('SQL statement is too long. It must be less

                than 8000 charachter!',16,1)
     RETURN 1
  END
  SET @Pivot_SQL = Left(@Pivot_SQL,Len(@Pivot_SQL)-1)

  -- PRINT @Pivot_SQL
  EXEC(@Pivot_SQL)

  RETURN 0
GO

-- Example use Northwind database --
-- Example 1 --
exec PivotWizard 'ShipCountry',
                 'YEAR(OrderDate)*100+Month(OrderDate)',
                 '[ExtendedPrice]',
                 'dbo.Invoices',
                 'OrderDate BETWEEN ''1996/01/01'' and ''1996/12/31'''

GO

-- Example 2 --
exec PivotWizard 'ProductName',
                 'ShipCountry',
                 '[ExtendedPrice]',
                 'dbo.Invoices',
                 'OrderDate BETWEEN ''1996/01/01'' and ''1996/12/31'''

-- =================================================
-- Pivot Table Wizard MySQL      (c) 2009 by GyurcIT
-- http://www.gyurcit.hu   e-mail: [e-mail]
-- =================================================

-- USE Database
DROP PROCEDURE IF EXISTS pivotwizard;

CREATE DEFINER = 'root'@'192.168.1.119'
PROCEDURE pivotwizard(
   IN P_Row_Field    VARCHAR(255),
   IN P_Column_Field VARCHAR(255),
   IN P_Value        VARCHAR(255),
   IN P_From         VARCHAR(4000),
   IN P_Where        VARCHAR(4000))
BEGIN
 DECLARE done INT DEFAULT 0;
 DECLARE M_Count_Columns int DEFAULT 0;
 DECLARE M_Column_Field varchar(60);
 DECLARE M_Columns VARCHAR(8000) DEFAULT '';
 DECLARE M_sqltext VARCHAR(8000);
 DECLARE M_stmt VARCHAR(8000);
 DECLARE cur1 CURSOR FOR SELECT CAST(Column_Field AS CHAR) FROM Temp;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 DROP TABLE IF EXISTS Temp;
 SET @M_sqltext = CONCAT('CREATE TEMPORARY TABLE Temp ',
                   ' SELECT DISTINCT ',P_Column_Field, 

				' AS Column_Field',
                   ' FROM ',P_From,
                   ' WHERE ',P_Where,
                   ' ORDER BY ', P_Column_Field);

 PREPARE M_stmt FROM @M_sqltext;
 EXECUTE M_stmt;

 SELECT COUNT(*) INTO M_Count_Columns 

 FROM Temp 

 WHERE Column_Field IS NOT NULL;

 IF (M_Count_Columns > 0) THEN
    OPEN cur1;
    REPEAT
      FETCH cur1 INTO M_Column_Field;
      IF (NOT done) and (M_Column_Field IS NOT NULL) THEN
         SET M_Columns = CONCAT(M_Columns,

' SUM( CASE WHEN ',P_Column_Field,'=''',M_Column_Field,'''',
         		' THEN ',P_Value,
                  ' ELSE 0 END) AS `', M_Column_Field ,'`,');

      END IF;
    UNTIL done END REPEAT;

    SET M_Columns = Left(M_Columns,Length(M_Columns)-1);
    SET @M_sqltext = CONCAT('SELECT ',P_Row_Field,',',M_Columns,
                            ' FROM ', P_From,
                            ' WHERE ', P_Where,
                            ' GROUP BY ', P_Row_Field,
                            ' ORDER BY ', P_Row_Field);

    PREPARE M_stmt FROM @M_sqltext;
    EXECUTE M_stmt;
  END IF;
END

-- Example:
CALL pivotwizard('article','Month(date)',
'netto',
'invoice',
'(year(date)=2009)')