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

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: gyurcit@gmail.com
-- =================================================

-- 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)‘)