(Resolvido) Como montar pivot table

[crazySerk]

Então Denis, porém na hora que eu faço um Insert eu crio esse historico... quando é insert não se usa o New? Porém o problema eu descobrio que era...

Eu declarei a varial cod_equipe e por alguma razao estava dando conflito com a atributo da tabela, mudei o nome para c_equipe e funcionou...

rsss

Porém posso aproveitar esse tópico e pedir um auxilio em outra coisa?

Estou precisando gerar um grafico com o desempenho geral da empresa no ano... exemplo teria 3 empresa e precisava mostrar o que cada uma produziu em cada mes exemplo...

Empresa 1 - Janeiro 23 milhoes...

Empresa 2 - Janeiro 33 milhes...

Empresa 3 - Janeiro 33 milhoes..

Empresa 1 - Fevereiro 13 milhoes...

Empresa 2 - Fevereiro 13 milhoes...

Empresa 3 - Fevereiro 13 milhoes...

E assim por diante...

Eu conseguiria montar isso pelo banco? se sim, voce teria um exemplo + - para eu estudar ?

Eu pesquisei um pouco e surgiu o tal pivot porém não entendi como aplicar ele em minha aplicação... Eu vi que eles consegue separar por mes porém na minha tabela eu salvo a data em Datetime, e como vou informar para o banco os meses correto? To confuso com isso.. rs

Minha tabela é está...

O Campo que quero mostrar é o valor_veiculo que no caso vai somar todos os veiculos do mes e apresentar exemplo Janeiro - 20 milhoes e assim por diante...

CREATE TABLE `propostas` (
  `cod_proposta` bigint(20) NOT NULL AUTO_INCREMENT,
  `cod_empresa` int(11) NOT NULL,
  `cod_loja` int(11) NOT NULL,
  `usuario` varchar(50) NOT NULL,
  `nome_completo` varchar(160) DEFAULT NULL,
  `cod_cliente` varchar(23) NOT NULL,
  `cod_cliente_avalista` varchar(23) DEFAULT NULL,
  `cod_modelo` bigint(20) DEFAULT NULL,
  `ano_fabricacao` varchar(4) DEFAULT NULL,
  `ano_modelo` varchar(4) DEFAULT NULL,
  `valor_veiculo` double DEFAULT NULL,
  `valor_tc` double DEFAULT NULL,
  `valor_emplacamento` double DEFAULT NULL,
  `valor_entrada` double DEFAULT NULL,
  `coeficiente` varchar(8) DEFAULT NULL,
  `num_parcelas` int(11) DEFAULT NULL,
  `valor_parcela` double DEFAULT NULL,
  `carencia` int(11) DEFAULT NULL,
  `data_cadastro` date DEFAULT NULL,
 

Bom não sei se ficou bem claro a minha duvida mas agradeço antecipadamente sua boa vontade ! ;D

Obrigado!

Editado Novembro 10, 2013 por crazySerk

[Denis Courcy]

Oi crazySerk,

para obter o ano e mes de uma data você pode usar a substring(campodata,1,7) ou combinar ano e mes com concat. Exemplo Concat(year(campodata),month(campodata)).

Para obter o faturamento do jeito que você quer, vou exemplificar em duas etapas. Siga-as para poder entender.

Vou usar o campo data_cadastro como base para data pois é o único campo data na tabela que você postou,

Fase 1.

Montando uma consulta que agrupe os valores por empresa e por competencia(ano/mes).

select cod_empresa, substring(data_cadastro,1,7) as competencia, sum(valor_veiculo) as valor
from propostas
group by cod_empresa, competencia;

Fase 2 Exemplo de pivot table. Passando competencia para titulo de coluna e representando empresa em linhas.

aplicando a procedure pivot table publicada em [link]http://scriptbrasil.com.br/forum/index.php?showtopic=150954[/link] e corrigida abaixo:

chamada do pivot table conforme o select acima.

call pivotwizard("empresa", "substring(data_cadastro,1,7)", "valor_veiculo", "propostas", "data_cadastro>'2011-01-01'");

abaixo o codigo corrigido do pivottable

DELIMITER $$;

DROP PROCEDURE IF EXISTS `sisamb`.`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 TEMPORARY 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 ;$$

[crazySerk]

Até a Fase 1 entendi, porém essa fase 2 não entendi nada.. rss
Ainda não mexi em nada de procedures...

Eu copio essa procedure e coloco no meu banco isso? se for, tentei executar não passou da primeira linha... rs

Deu esse erro.. :

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 V' at line 1

[Denis Courcy]

Em "DROP PROCEDURE IF EXISTS `sisamb`.`pivotwizard`$$", "`sisamb`" é o nome do meu banco de teste. Use nome do seu banco de dados.

remova este trecho de código da procedure. Ele corresponde a um usuário local em meu banco de teste.

Eu utilizo o SQLyog como gerenciador de banco de dados MySQL. Ele utiliza esta notação "DELIMITER $$;". Dependendo do gerenciador de bd que você utiliza use "DELIMITER $$" no lugar de "DELIMITER $$;" e use "DELIMITER ;" no lugar de "DELIMITER ;$$"

[crazySerk]

Entendi, vou fazer os testes aqui e retorno o que deu.. obrigado até o momento Denis!

[crazySerk]

Denis, baixei tambem o SQLyog para executar sua procedure, alterei o banco de dados, porém está dando este erro ainda:

1 queries executed, 0 success, 1 errors, 0 warnings

Query: DROP PROCEDURE IF EXISTS `iwebctrl`.`pivotwizard`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `pivotwizard`(IN P_Row_Field VAR...

Error Code: 1064
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 V' at line 1

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.001 sec

Estou usando a versão 5.5 do mysql não sei se tem algum problema...

[Denis Courcy]

Você colocou a linha "DELIMITER $$;" no início e a linha "

DELIMITER ;$$" no fim?

[crazySerk]

Sim deixei igual o que você postou, apenas mudei o banco de dados, como estou usando o mesmo gerenciador da BD que voce, deixei tudo igual.

[Denis Courcy]

Ok. Baixei uma versão mais atualizada para testes em minha máquina de teste. A versão que uso é a 5.13. Muito antiga.

A versão community que baixei para teste foi a 7.02. Houve uma modificação. Agora eles usam "DELIMITER $$" no início e "DELIMITER ;" no final.

[crazySerk]

Caraca denis... ficou top!!! funcionou mesmo...

Denis, não quero abusar da sua boa vontade, gostaria de saber se é possivel você comentar essas linhas dessa procedure porque com certeza irei usar para fazer outras dessa no sistema..

E uma outra coisa, por exemplo eu to chamando assim:

call pivotwizard("cod_loja", "substring(data_cadastro,1,7)", "valor_veiculo", "propostas", "cod_empresa = 6");

neste caso está mostrando dessa maneira o resultado:

cod_loja | 2013-10 | 2013-11

54 | 40000 | 500000

Como posso fazer para pegar o nome dessa empresa cod_loja que está em outra tabela? que no caso é na tabela "empresas_lojas"

Quero agradecer novamente por me ajudar... obrigado pela sua atenção!!!

[Denis Courcy]

Observe o Select que fiz na primeira fase. Veja como coloquei cada parte dele no pivot table.

Faça outro select,igual ao da primeira fase e inclua o nome da empresa, e me mostre o que você conseguiu.

Eu te orientarei como montar o pivot table

Eu já sei o resultado. o objetivo é que você descubra e aprenda.

[crazySerk]

Não sei se é correto mas montei assim.. :P

CALL pivotwizard("a.cod_loja, b.nome", "substring(data_cadastro,1,7)", "valor_veiculo", "propostas a", " left outer join empresas_lojas b on

(b.cod_loja = a.cod_loja) where a.cod_empresa = 6");

Retirei o ' WHERE ' deixei apenas a variavel assim eu consigo passar os comandos do join antes do where ...

Pode fechar esse tópico Denis!

Obrigado pela sua atenção