(Resolvido) Procedure - Pivot

[Supernoi]

Bom dia,

Estou precisando fazer pivot em algumas views no mysql, na mao (case) consome muito tempo alem de ser muiitooo chato...

pesquisei na internet se havia alguma procedure que ajudasse a fazer isso...

A que encontrei não consigo executar, da erros estranhos(para mim que sou leigo com mysql)

Me deram a dica de tentar mudar o SQL_MODE, mudei... mas também não consegui

Utilizo o MySql 5.1.23-rc-community

encontrei esta procedure abaixo:

-- =================================================
-- Pivot Table Wizard MySQL © 2009 by GyurcIT
-- [url=http://www.gyurcit.hu]http://www.gyurcit.hu[/url] e-mail: gyurcit@gmail.com
-- =================================================

-- USE Database
DROP PROCEDURE IF EXISTS pivotwizard;

CREATE DEFINER = 'root'@'Localhost'
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

aqui da os seguintes erros:

Script line: 10 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 9

Script line: 19 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE M_Count_Columns int DEFAULT 0' at line 1

Script line: 20 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE M_Column_Field varchar(60)' at line 1

Script line: 21 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE M_Columns VARCHAR(8000) DEFAULT ''' at line 1

Script line: 22 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE M_sqltext VARCHAR(8000)' at line 1

Script line: 23 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE M_stmt VARCHAR(8000)' at line 1

Script line: 24 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE cur1 CURSOR FOR SELECT CAST(Column_Field AS CHAR) FROM Temp' at line 1

Script line: 25 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1' at line 1

Script line: 28 Unknown column 'P_Column_Field' in 'field list'

Script line: 36 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

Script line: 37 Unknown prepared statement handler (M_stmt) given to EXECUTE

Script line: 39 Undeclared variable: M_Count_Columns

Script line: 45 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (M_Count_Columns > 0) THEN

OPEN cur1' at line 1

Script line: 47 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REPEAT

FETCH cur1 INTO M_Column_Field' at line 1

Script line: 49 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (NOT done) and (M_Column_Field IS NOT NULL) THEN

SET M_Columns = CO' at line 1

Script line: 56 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1

Script line: 57 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNTIL done END REPEAT' at line 1

Script line: 59 Unknown system variable 'M_Columns'

Script line: 60 Unknown column 'P_Row_Field' in 'field list'

Script line: 66 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

Script line: 67 Unknown prepared statement handler (M_stmt) given to EXECUTE

Script line: 68 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1

Script line: 69 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1

alguém faz ideia do porque desses erros?

Alguma dica? Uma luz?!

Obrigado.

Editado Junho 16, 2010 por Denis Courcy
Melhorar entendimento do código

[Denis Courcy]

Oi, 'Supernoi'

Só corrigi a sintaxe. Não testei o funcionamento.

Faça assim:

DELIMITER $$;
DROP PROCEDURE IF EXISTS pivotwizard$$

CREATE DEFINER = 'root'@'Localhost' 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$$
DELIMITER;$$

[Supernoi]

Denis, bom dia...

realmente melhorou muito...

agora so estou com o erro:

Script line: 2 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE DEFINER = 'root'@'Localhost' PROCEDURE pivotwizard(

IN P_Row_Field VA' at line 3

meu servidor esta com: set global sql_mode ='ansi'

Obrigado pela atencao.

[Supernoi]

Denis Funcionou... so ficou faltando uns ";"

mais agora ta funcionando certinho...

Muito obrigado pela a ajuda...

Tenha um bom dia...

como ficou:

DELIMITER $$;
DROP PROCEDURE IF EXISTS pivotwizard$$;

CREATE DEFINER = 'root'@'Localhost' 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_pivot;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

   DROP TABLE IF EXISTS temp_pivot;
   SET @M_sqltext = CONCAT('CREATE temp_pivotORARY TABLE temp_pivot ',
                           ' 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_pivot
   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$$;
DELIMITER;$$

[Denis Courcy]

Oi, Além do comando para desalocar a área de memória alocara pelo comando PREPARE, não encontrei nada de errado.

Inclusive já testei e está funcionando corretamente.

Segue o código com a instrução que estava faltando:

DELIMITER $$;

DROP PROCEDURE IF EXISTS `pivotwizard`$$

CREATE DEFINER=`root`@`Localhost` 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;
   DEALLOCATE PREPARE stmt;
END$$

DELIMITER;$$