How to group results from a row query to columns

Question

Good afternoon,

have this query

SELECT
                tblcliente.idCliente,
                tblclienterespostapre.resposta
                FROM
                tblcliente
                INNER JOIN tblclienterespostapre ON tblclienterespostapre.idCliente = tblcliente.idCliente
                INNER JOIN tblpergunta ON tblclienterespostapre.idPergunta = tblpergunta.idPergunta
                WHERE
                tblpergunta.idPergunta = 16 OR
                tblpergunta.idPergunta = 34 OR
                tblpergunta.idPergunta = 35 AND
                tblcliente.estado = 1

that returns this result to me

i needed that instead of returning me 6 rows in two columns I return two rows in 3 columns, this kind:

Linha 1: 42 | andre da silva | 930000000 | [email protected]
linha 2: 43 | cliente numero 2              | telefone  | email |

This is my database template

this is basically an application that manages users, these users are composed by several questions in which the questions can have several characters, as typodequestion, whether it is published or not, whether it was validated or not, these questions are dynamic, or new questions can be added to all customers.

It’s a little confusing :

if it were possible to name the columns it was also an asset

Thank you

Answer 1

Hello, you can this using a cursor to do this.

see how it would look.

declare @tabela table
(
    id int ,
    resposta varchar(100)
)

declare @usuario table
(
    id int ,
    nome varchar(100),
    telefone varchar(100) null,
    email varchar(100) null
)

insert into @tabela values
(1, 'joao'),
(1, '3232323'),
(1, '[email protected]'),

(2, 'paulo'),
(2, '3232323'),
(2, '[email protected]')

DECLARE  @id int
DECLARE @resposta VARCHAR(100)
declare @index int = 0;

DECLARE cursor_objects CURSOR FOR
select id,  resposta as nome from @tabela

-- Abrindo Cursor para leitura
OPEN cursor_objects

-- Lendo a próxima linha
FETCH NEXT FROM cursor_objects INTO @id, @resposta

-- Percorrendo linhas do cursor (enquanto houverem)
WHILE @@FETCH_STATUS = 0
BEGIN

   if(@index = 0)
        insert into @usuario values (@id, @resposta, '','')
    if (@index = 1)
        update @usuario set telefone = @resposta where id = @id
    if (@index = 2)
        update @usuario set email = @resposta where id = @id

    set @index = @index + 1;

    if(@index > 2)
        set @index = 0;

    -- Lendo a próxima linha
    FETCH NEXT FROM cursor_objects INTO  @id, @resposta
END

-- Fechando Cursor para leitura
CLOSE cursor_objects

-- Desalocando o cursor
DEALLOCATE cursor_objects

select * from @usuario

Answer 2

The staff presented a solution to your problem:

  # Versao estatica
  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;

Here is the example working