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, 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.

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: [email protected] -- ================================================= -- 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.