To group and remove lines with "null" values from this query in Mysql

Question

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: http://sqlfiddle.com/#! 9/8332c/15

The idea is to put in pivot format (reversing the rows to columns), this query it returns vertically 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;

Answer 1

If I understand your question right you’re trying to make that 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;

Upshot

---

If the idea was to generate the query SQL dynamically from a list of questions ids you were on the right track:

SET @id_perguntas = '16,34,35';
SET @sql = NULL;

SELECT GROUP_CONCAT(
    CONCAT('MAX(IF(cli_resp.idPergunta = ', idPergunta, ', resp.resposta, NULL))',
           ' AS `', pergunta, '`'))
INTO @sql
FROM
  tblPergunta
WHERE FIND_IN_SET(idPergunta, @id_perguntas)
ORDER BY idPergunta;

SET @sql = CONCAT(
    'SELECT
       cli_resp.idCliente,',
       @sql,
    '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 (', @id_perguntas, ')',
    'GROUP BY cli.idCliente
     ORDER BY cli.idCliente');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Functional example in Sqlfiddle

---

Source: Soen - Mysql pivot table query with Dynamic Columns

Answer 2

This query returns the online questions.

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