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.
