How to group results from a row-to-column query

Question

How to group results from a row-to-column query

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

Good afternoon,

I 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 6 rows in two columns I would return two rows in 3 columns, of this sort:

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

This is my database template

This basically is an application that generates users, these users are composed of several questions in which the questions can have several characteristics, like type of question, whether it is public or not, whether it has been validated or not, these questions are dynamic, that is new questions can be added for all clients.

It's a bit confusing: s

If it were possible to name the columns it was also an added value

Thank you

mysql sql-select select mysqli

asked by anonymous 28.10.2015 / 16:18

2 answers

Using functions Updating MainActivity Items

score 1 · Answer 1

Hello, you can use a cursor to do this.

See the example as 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

score 1 · Answer 2

The staff provided 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 working example