Jump to content
Fórum Script Brasil
  • 0

(Resolvido) Como montar pivot table


crazySerk

Question

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!

Edited by crazySerk
Link to comment
Share on other sites

11 answers to this question

Recommended Posts

  • 0

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 ;$$
Link to comment
Share on other sites

  • 0

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
Link to comment
Share on other sites

  • 0

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 ;$$"

Link to comment
Share on other sites

  • 0

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

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0

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!!!

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0

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
Link to comment
Share on other sites

Guest
This topic is now closed to further replies.


  • Forum Statistics

    • Total Topics
      152k
    • Total Posts
      651.5k
×
×
  • Create New...