sort rows in columns in postgresql

Question

sort rows in columns in postgresql

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I have a table where I keep track of nutritional evaluations of people, each person can have between 1 to 6 evaluations and all are stored in that table, what I want to do is that moment to download this information bring me the data of the person 1 time but the result of each visit is the one that is added for example

nombre|apellido|telefono|fecha_evaluacion|resultado_evaluacion
pedro |perez   |5558877 | 05/05/2017     | desnutricion
pedro |perez   |5558877 | 05/07/2017     | adecuado

and I need you to return it to me in the following way

nombre|apellido|telefono|fecha_evaluacion1|resultado_evaluacion1|fecha_evaluacion2 | resultado_evaluacion2
pedro | perez  |5558877 |  05/05/2017     | desnutricion        | 05/07/2017       | Adecuado

Each person can have up to 6 evaluations and I am only interested in repeating the date of each evaluation and its diagnosis.

postgresql

asked by Angel Gutierrez 18.10.2017 в 17:45

source

3 answers

Play video with VLC wpf show values of one method in another method with php oriented to objects

score 0 · Answer 1

Maybe you can try something like this

select *
from crosstab (
    'select id, name, value
    from tt1
    order by 1,2')
AS (row_name int, col1 text, col2 text, col3 text, col4 text);

Where you can select the rows you want in the form of columns

score 0 · Answer 2

One possible way is to number each evaluation from 1 to n (where n is a fixed maximum number, as you mention 6) and then do a GROUP BY combined with some conditional aggregation function. I show you an example based on the data you contributed where you would only have two sets of columns to be transposed, but it is not difficult to adapt it to a greater amount:

We create the data first:

CREATE TABLE Ejemplo (
  nombre                  VARCHAR(255),
  apellido                VARCHAR(255),
  telefono                VARCHAR(255),
  fecha_evaluacion        DATE,
  resultado_evaluacion    VARCHAR(255)
);

INSERT INTO Ejemplo (nombre, apellido, telefono, fecha_evaluacion, resultado_evaluacion)
VALUES ('pedro','perez','5558877', '05/05/2017', 'desnutricion'),
       ('pedro','perez','5558877', '05/07/2017', 'adecuado');

And here the query:

SELECT M.nombre, 
       M.apellido,
       MAX(CASE WHEN M.Nro = 1 THEN fecha_evaluacion ELSE NULL END)     AS Fecha1,
       MAX(CASE WHEN M.Nro = 1 THEN resultado_evaluacion ELSE NULL END) AS Resultado1,
       MAX(CASE WHEN M.Nro = 2 THEN fecha_evaluacion ELSE NULL END)     AS Fecha2,
       MAX(CASE WHEN M.Nro = 2 THEN resultado_evaluacion ELSE NULL END) AS Resultado2
       FROM ( SELECT nombre, 
                     apellido, 
                     fecha_evaluacion, 
                     resultado_evaluacion,
                     ROW_NUMBER() OVER (PARTITION BY nombre, apellido ORDER BY fecha_evaluacion) AS Nro
                     FROM Ejemplo
       ) M
       GROUP BY M.nombre, 
                M.apellido;

The exit:

+--------+----------+------------+--------------+------------+------------+
| nombre | apellido | fecha1     | resultado1   | fecha2     | resultado2 |
+--------+----------+------------+--------------+------------+------------+
| pedro  | perez    | 2017-05-05 | desnutricion | 2017-05-07 | adecuado   |
+--------+----------+------------+--------------+------------+------------+

score 0 · Answer 3

Your database should be two entities (tables), one "people" and another "follow-ups." When trying to display two columns for each tracking, you have to keep in mind that you must define the two columns, since SQL can not create columns on its own. So if you want to show the six follow-ups, you must define the other columns that you want to show, in this case they would be 12 and the query would basically be the following.

      SELECT p.nombre, p.apellido, p.telefono,
      (SELECT se.fecha_evaluacion FROM seguimientos se WHERE se.id_persona=p.id_persona LIMIT 0,1) AS fecha_evaluacion1,
      (SELECT se.resultado_evaluacion FROM seguimientos se WHERE se.id_persona=p.id_persona LIMIT 0,1) AS resultado_evaluacion1,
      (SELECT se.fecha_evaluacion FROM seguimientos se WHERE se.id_persona=p.id_persona LIMIT 1,1) AS fecha_evaluacion2,
      (SELECT se.resultado_evaluacion FROM seguimientos se WHERE se.id_persona=p.id_persona LIMIT 1,1) AS resultado_evaluacion2,
      (SELECT se.fecha_evaluacion FROM seguimientos se WHERE se.id_persona=p.id_persona LIMIT 2,1) AS fecha_evaluacion3,
      (SELECT se.resultado_evaluacion FROM seguimientos se WHERE se.id_persona=p.id_persona LIMIT 2,1) AS resultado_evaluacion3,
      (SELECT se.fecha_evaluacion FROM seguimientos se WHERE se.id_persona=p.id_persona LIMIT 3,1) AS fecha_evaluacion4,
      (SELECT se.resultado_evaluacion FROM seguimientos se WHERE se.id_persona=p.id_persona LIMIT 3,1) AS resultado_evaluacion4,
      (SELECT se.fecha_evaluacion FROM seguimientos se WHERE se.id_persona=p.id_persona LIMIT 4,1) AS fecha_evaluacion5,
      (SELECT se.resultado_evaluacion FROM seguimientos se WHERE se.id_persona=p.id_persona LIMIT 4,1) AS resultado_evaluacion5,
      (SELECT se.fecha_evaluacion FROM seguimientos se WHERE se.id_persona=p.id_persona LIMIT 5,1) AS fecha_evaluacion6,
      (SELECT se.resultado_evaluacion FROM seguimientos se WHERE se.id_persona=p.id_persona LIMIT 5,1) AS resultado_evaluacion6,
      FROM personas p INNER JOIN seguimientos s ON(p.id_persona=s.id_persona)
      GROUP BY p.nombre, p.apellido, p.telefono