multiples - sql oracle convertir columnas en filas

SQL transponen filas como columnas

Question

Tengo un enigma interesante que creo que se puede resolver en puramente SQL. Tengo tablas similares a las siguientes:

responses:

user_id | question_id | body
----------------------------
1       | 1           | Yes
2       | 1           | Yes
1       | 2           | Yes
2       | 2           | No
1       | 3           | No
2       | 3           | No


questions:

id | body
-------------------------
1 | Do you like apples?
2 | Do you like oranges?
3 | Do you like carrots?

y me gustaría obtener la siguiente salida

user_id | Do you like apples? | Do you like oranges? | Do you like carrots?
---------------------------------------------------------------------------
1       | Yes                 | Yes                  | No
2       | Yes                 | No                   | No

No sé cuántas preguntas habrá y serán dinámicas, así que no puedo codificar todas las preguntas. Estoy usando PostgreSQL y creo que esto se llama transposición, pero parece que no puedo encontrar nada que diga la forma estándar de hacerlo en SQL. Recuerdo haberlo hecho en mi clase de base de datos en la universidad, pero estaba en MySQL y sinceramente no recuerdo cómo lo hicimos.

Supongo que será una combinación de uniones y una declaración GROUP BY , pero ni siquiera puedo descubrir cómo comenzar.

¿Alguien sabe cómo hacer esto? ¡Muchas gracias!

Editar 1: Encontré información sobre el uso de una crosstab que parece ser lo que quiero, pero tengo problemas para encontrarle sentido. ¡Los enlaces a mejores artículos serán muy apreciados!

Answer 1

Escribí una función para generar la consulta dinámica. Genera el sql para la tabla cruzada y crea una vista (lo deja caer primero si existe). Puede seleccionar de la vista para obtener sus resultados.

Aquí está la función:

CREATE OR REPLACE FUNCTION public.c_crosstab (
  eavsql_inarg varchar,
  resview varchar,
  rowid varchar,
  colid varchar,
  val varchar,
  agr varchar
)
RETURNS void AS
$body$
DECLARE
    casesql varchar;
    dynsql varchar;    
    r record;
BEGIN   
 dynsql='''';

 for r in 
      select * from pg_views where lower(viewname) = lower(resview)
  loop
      execute ''DROP VIEW '' || resview;
  end loop;   

 casesql=''SELECT DISTINCT '' || colid || '' AS v from ('' || eavsql_inarg || '') eav ORDER BY '' || colid;
 FOR r IN EXECUTE casesql Loop
    dynsql = dynsql || '', '' || agr || ''(CASE WHEN '' || colid || ''='''''' || r.v || '''''' THEN '' || val || '' ELSE NULL END) AS '' || agr || ''_'' || r.v;
 END LOOP;
 dynsql = ''CREATE VIEW '' || resview || '' AS SELECT '' || rowid || dynsql || '' from ('' || eavsql_inarg || '') eav GROUP BY '' || rowid;
 RAISE NOTICE ''dynsql %1'', dynsql; 
 EXECUTE dynsql;
END

$body$
LANGUAGE ''plpgsql''
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Y así es como lo uso:

SELECT c_crosstab(''query_txt'', ''view_name'', ''entity_column_name'', ''attribute_column_name'', ''value_column_name'', ''first'');

Ejemplo: puño ejecutado:

SELECT c_crosstab(''Select * from table'', ''ct_view'', ''usr_id'', ''question_id'', ''response_value'', ''first'');

Que:

Select * from ct_view;

Answer 2

Hay un ejemplo de esto en contrib/tablefunc/ .

Answer 3

Implementé una función realmente dinámica para manejar este problema sin tener que codificar ninguna clase específica de respuestas ni usar módulos externos / extensiones. También brinda control total sobre el orden de columnas y admite múltiples claves y columnas de clase / atributo.

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

Ejemplo que resuelve este problema en particular:

begin;

create temporary table responses (
    user_id integer,
    question_id integer,
    body text
) on commit drop;

create temporary table questions (
    id integer,
    body text
) on commit drop;

insert into responses values (1,1,''Yes''), (2,1,''Yes''), (1,2,''Yes''), (2,2,''No''), (1,3,''No''), (2,3,''No'');
insert into questions values (1, ''Do you like apples?''), (2, ''Do you like oranges?''), (3, ''Do you like carrots?'');

select colpivot(''_output'', $$
    select r.user_id, q.body q, r.body a from responses r
        join questions q on q.id = r.question_id
$$, array[''user_id''], array[''q''], ''#.a'', null);

select * from _output;

rollback;

Esto produce:

 user_id | ''Do you like apples?'' | ''Do you like carrots?'' | ''Do you like oranges?'' 
---------+-----------------------+------------------------+------------------------
       1 | Yes                   | No                     | Yes
       2 | Yes                   | No                     | No

Answer 4

Puede resolver este ejemplo con la función de crosstab de esta manera

drop table if exists responses;
create table responses (
user_id integer,
question_id integer,
body text
);

drop table if exists questions;
create table questions (
id integer,
body text
);

insert into responses values (1,1,''Yes''), (2,1,''Yes''), (1,2,''Yes''), (2,2,''No''), (1,3,''No''), (2,3,''No'');
insert into questions values (1, ''Do you like apples?''), (2, ''Do you like oranges?''), (3, ''Do you like carrots?'');

select * from crosstab(''select responses.user_id, questions.body, responses.body from responses, questions where questions.id = responses.question_id order by user_id'') as ct(userid integer, "Do you like apples?" text, "Do you like oranges?" text, "Do you like carrots?" text);

Primero, debe instalar la extensión tablefunc. Desde la versión 9.1 puedes hacerlo usando create extension:

CREATE EXTENSION tablefunc;

Answer 5

Utilizar:

  SELECT r.user_id,
         MAX(CASE WHEN r.question_id = 1 THEN r.body ELSE NULL END) AS "Do you like apples?",
         MAX(CASE WHEN r.question_id = 2 THEN r.body ELSE NULL END) AS "Do you like oranges?",
         MAX(CASE WHEN r.question_id = 3 THEN r.body ELSE NULL END) AS "Do you like carrots?"
    FROM RESPONSES r
    JOIN QUESTIONS q ON q.id = r.question_id
GROUP BY r.user_id

Esta es una consulta de pivote estándar, porque está "pivotando" los datos de las filas a los datos en columnas.