guocongwudi / mysql-pivot-procedure Goto Github PK

The replace statement made an error
Would you like to work on some more? I could need a total sum of columns to 
sort the results.
-------------------------------------
CREATE DEFINER = 'root'@'%'
PROCEDURE npreports.pivotwizard_text(
   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))
ThisSP: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);

 #SELECT @M_sqltext;LEAVE ThisSP;
 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,

    ' REPLACE( GROUP_CONCAT( CASE WHEN ',P_Column_Field,'=''',M_Column_Field,'''',
                ' THEN ',P_Value,
                  ' ELSE NULL END, ''''), '','', '''') AS ''', M_Column_Field ,''',');

      END IF;
    UNTIL done END REPEAT;

    SET M_Columns = Left(M_Columns,Length(M_Columns)-1);
#SELECT M_Columns;LEAVE ThisSP;
    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);

 #SELECT @M_sqltext;LEAVE ThisSP;
 PREPARE M_stmt FROM @M_sqltext;
    EXECUTE M_stmt;
  END IF;
END