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;