How to group and remove rows with null values of this query in MySQL

2
CREATE TABLE tblCliente (
idCliente int(11),
estado int(1)
);

CREATE TABLE tblPergunta (
idPergunta int,
idCategoria int,
pergunta varchar(255),
tipoResposta tinyint(1)
);

CREATE TABLE tblClienteRespostaPre (
idCliente int(11),
idPergunta int(11),
idRespostaPre int(11),
privacidade int(1),
verificada int(1)
);

CREATE TABLE tblRespostaPre (
idRespostaPre int(11),
idPergunta int(11),
resposta varchar(255)
);


insert into tblCliente values
(1, 1),
(2, 1),
(3, 1);

insert into tblPergunta values
(16, 1, 'qual o seu telefone?',1),
(34, 4, 'qual o seu email?',   1),
(35, 2, 'você é de são paulo?',1);

insert into tblClienteRespostaPre values
(1, 16, 1, 1, 1),
(1, 34, 2, 1, 1),
(1, 35, 3, 1, 1),
(2, 16, 4, 1, 1),
(2, 34, 5, 1, 1),
(2, 35, 6, 1, 1),
(3, 16, 7, 1, 1),
(3, 34, 8, 1, 1),
(3, 35, 9, 1, 1);

insert into tblRespostaPre values
(1, 16, '123456789'),
(2, 34, '[email protected]'),
(3, 35, 'sim'),
(4, 16, '568723658'),
(5, 34, '[email protected]'),
(6, 35, 'não'),
(7, 16, '687678677'),
(8, 34, '[email protected]'),
(9, 35, 'Claro');

SET @sql = NULL;
SET @count := 0;
SELECT GROUP_CONCAT(DISTINCT CONCAT(
      'CASE WHEN tblRespostaPre.idPergunta = ', idPergunta,' THEN \'', resposta,'\'
       ELSE  null END AS \'Resposta ',@count:=@count + 1,'\'')
  )
INTO @sql
FROM
  tblRespostaPre where idPergunta IN (16,34,35);

SET @sql = CONCAT('SELECT tblCliente.idCliente,
                          tblPergunta.idPergunta,
                          ',@sql,' 
                  FROM tblCliente 
                  INNER JOIN tblClienteRespostaPre ON (tblClienteRespostaPre.idCliente=tblCliente.idCliente)
                  INNER JOIN tblPergunta           ON (tblPergunta.idPergunta=tblClienteRespostaPre.idPergunta)
                  INNER JOIN tblRespostaPre        ON (tblRespostaPre.idPergunta=tblClienteRespostaPre.idPergunta)
                  WHERE  tblPergunta.idPergunta    IN (16,34,35) AND tblCliente.estado=1 
                  group by tblPergunta.idPergunta, tblCliente.idCliente');

PREPARE stmt FROM @sql;
EXECUTE stmt;

Sample code: link

The idea is to put pivot (inverting rows to columns), this query it vertically returns the questions, but would have to be horizontal:

SELECT cli.idCliente as ID_CLIENTE,
       perg.idPergunta as ID_PERGUNTA,
       resp.idRespostaPre as ID_RESPOSTA,
       perg.pergunta as PERGUNTA,
       resp.resposta as RESPOSTA
FROM   tblCliente cli  
       INNER JOIN tblClienteRespostaPre cli_resp
       ON (cli_resp.idCliente=cli.idCliente)
       LEFT JOIN tblPergunta perg
       ON (perg.idPergunta=cli_resp.idPergunta)
       LEFT JOIN tblRespostaPre resp
       ON (resp.idPergunta=cli_resp.idPergunta)
WHERE  perg.idPergunta in(16,34,35) AND cli.estado=1;
    
asked by anonymous 29.10.2015 / 19:02

2 answers

6

If I understand your right question you are trying to do this query here:

SELECT
  cli_resp.idCliente,
  MAX(IF(cli_resp.idPergunta = 16, resp.resposta, NULL)) AS 'Qual o seu telefone?',
  MAX(IF(cli_resp.idPergunta = 34, resp.resposta, NULL)) AS 'Qual o seu email?',
  MAX(IF(cli_resp.idPergunta = 35, resp.resposta, NULL)) AS 'Você é de são paulo?'
FROM tblCliente cli
  INNER JOIN tblClienteRespostaPre cli_resp
    ON (cli.idCliente = cli_resp.idCliente)
  LEFT JOIN tblRespostaPre resp
    ON (cli_resp.idRespostaPre = resp.idRespostaPre)
WHERE cli.estado = 1 AND cli_resp.idPergunta IN (16, 34, 35)
GROUP BY cli.idCliente
ORDER BY cli.idCliente;

Result

IftheideawastogeneratequerySQLdynamicallyfromalistofidsofthequestionsyouwereontherighttrack:

SET@id_perguntas='16,34,35';SET@sql=NULL;SELECTGROUP_CONCAT(CONCAT('MAX(IF(cli_resp.idPergunta=',idPergunta,',resp.resposta,NULL))','AS'',pergunta,'''))INTO@sqlFROMtblPerguntaWHEREFIND_IN_SET(idPergunta,@id_perguntas)ORDERBYidPergunta;SET@sql=CONCAT('SELECTcli_resp.idCliente,',@sql,'FROMtblClientecliINNERJOINtblClienteRespostaPrecli_respON(cli.idCliente=cli_resp.idCliente)LEFTJOINtblRespostaPrerespON(cli_resp.idRespostaPre=resp.idRespostaPre)WHEREcli.estado=1ANDcli_resp.idPerguntaIN(',@id_perguntas,')','GROUPBYcli.idClienteORDERBYcli.idCliente');PREPAREstmtFROM@sql;EXECUTEstmt;DEALLOCATEPREPAREstmt;

Functional sample in SQLFiddle

Source : SOen - MySQL pivot table query with dynamic columns

    
04.11.2015 / 23:44
0

This query returns the questions online.

select a.idCliente,
       GROUP_CONCAT(distinct c.idPergunta),
       GROUP_CONCAT(distinct d.idRespostaPre),
       GROUP_CONCAT(distinct c.pergunta),
       GROUP_CONCAT(distinct d.resposta)
  from tblCliente a,
       tblClienteRespostaPre b,
       tblPergunta c,
       tblRespostaPre d
 where a.idCliente = b.idCliente
   and b.idPergunta = c.idPergunta
   and b.idPergunta = d.idPergunta
   and a.estado = 1
   and b.idPergunta in (16, 34, 35)
 group by a.idCliente
 order by 1, 2, 3, 4, 5
    
04.11.2015 / 21:57