multiples - sql transponer columnas a filas

Transposición de un resultado sql para que una columna pase a varias columnas

Question

Estoy tratando de obtener datos de una tabla para una encuesta en un formato particular. Sin embargo, todos mis intentos parecen entregar el DB debido a demasiadas uniones / demasiado pesadas en el DB.

Mi información se ve así:

id, user, question_id, answer_id, 
1,   1,   1,           1
3,   1,   3,           15
4,   2,   1,           2
5,   2,   2,           12
6,   2,   3,           20

Hay aproximadamente 250,000 filas y cada usuario tiene alrededor de 30 filas. Quiero que el resultado se vea así:

user0, q1, q2,   q3 
1,     1,  NULL, 15
2,     2,  12,   20 

De modo que cada usuario tenga una fila en el resultado, cada una con una columna separada para cada respuesta.

Estoy usando Postgres pero las respuestas en cualquier lenguaje SQL serían apreciadas ya que podría traducir a Postgres.

EDITAR: también necesito poder tratar con usuarios que no responden preguntas, es decir, en el ejemplo anterior q2 para el usuario 1.

Answer 1

La respuesta de Erwins es buena, hasta que aparece la respuesta faltante para un usuario. Voy a hacer una suposición sobre ti ... tienes una tabla de usuarios que tiene una fila por usuario y tienes una tabla de preguntas que tiene una fila por pregunta.

select usr, question_id
from users u inner join questions q on 1=1
order by 1,

Esta declaración creará una fila para cada usuario / pregunta y estará en el mismo orden. Conviértalo en una subconsulta y deja que se una a tus datos ...

select usr,question_id,qa.answer_id
from
(select usr, question_id
from users u inner join questions q on 1=1
)a
left join qa on qa.usr = a.usr and qa.question_id = a.usr
order by 1,2

Enchúfalo a la declaración de referencias cruzadas de Erwins y dale crédito por la respuesta: P

Answer 2

Considere la siguiente demostración:

CREATE TEMP TABLE qa (id int, usr int, question_id int, answer_id int);
INSERT INTO qa VALUES
 (1,1,1,1)
,(2,1,2,9)
,(3,1,3,15)
,(4,2,1,2)
,(5,2,2,12)
,(6,2,3,20);

SELECT *
FROM   crosstab(''
    SELECT usr::text
          ,question_id
          ,answer_id
    FROM qa
    ORDER BY 1,2'')
 AS ct (
     usr text
    ,q1 int
    ,q2 int
    ,q3 int);

Resultado:

 usr | q1 | q2 | q3
-----+----+----+----
 1   |  1 |  9 | 15
 2   |  2 | 12 | 20
(2 rows)

user es una palabra reservada ¡No lo use como nombre de columna! Lo renombré para usr .

Necesita instalar el módulo adicional tablefunc que proporciona la función crosstab() referencias crosstab() . Tenga en cuenta que esta operación es estrictamente por base de datos . En PostgreSQL 9.1 puede simplemente:

CREATE EXTENSION tablefunc;

Para la versión anterior, ejecutaría un script de shell proporcionado en su directorio contrib . En Debian, para PostgreSQL 8.4 , eso sería:

psql mydb -f /usr/share/postgresql/8.4/contrib/tablefunc.sql

Answer 3

Implementé una función realmente dinámica para manejar este problema sin tener que codificar un número específico de preguntas o usar módulos / extensiones externas. También es mucho más fácil de usar que la crosstab() .

Puede encontrarlo aquí: https://github.com/jumpstarter-io/colpivot

Ejemplo que resuelve este problema en particular:

begin;

create temp table qa (id int, usr int, question_id int, answer_id int);
insert into qa values
 (1,1,1,1)
,(2,1,2,9)
,(3,1,3,15)
,(4,2,1,2)
,(5,2,2,12)
,(6,2,3,20);

select colpivot(''_output'', $$
    select usr, (''q'' || question_id::text) question_id, answer_id from qa
$$, array[''usr''], array[''question_id''], ''#.answer_id'', null);

select * from _output;

rollback;

Resultado:

 usr | ''q1'' | ''q2'' | ''q3'' 
-----+------+------+------
   1 |    1 |    9 |   15
   2 |    2 |   12 |   20
(2 rows)