SQL Transpose Rows come colonne

Ho un enigma interessante che credo possa essere risolto in puro SQL. Ho tabelle simili alle seguenti:

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? 

e mi piacerebbe ottenere il seguente risultato

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

Non so quante domande ci saranno, e saranno dinamiche, quindi non posso solo codificare per ogni domanda. Sto usando PostgreSQL e credo che questo si chiama trasposizione, ma non riesco a trovare nulla che dice il modo standard di farlo in SQL. Ricordo di averlo fatto nella mia class di database al college, ma era in MySQL e sinceramente non ricordo come lo abbiamo fatto.

Presumo che sarà una combinazione di join e una dichiarazione GROUP BY , ma non riesco nemmeno a capire come iniziare.

Qualcuno sa come fare questo? Grazie mille!

Modifica 1: ho trovato alcune informazioni sull’utilizzo di un campo incrociato che sembra essere quello che voglio, ma ho difficoltà a dare un senso a ciò. Link a articoli migliori sarebbero molto apprezzati!

Uso:

  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 

Questa è una query pivot standard, perché stai “ruotando” i dati dalle righe ai dati colonnari.

Ho implementato una funzione veramente dynamic per gestire questo problema senza dover codificare hard qualsiasi class specifica di risposte o utilizzare moduli / estensioni esterni. Assicura inoltre il pieno controllo sull’ordinamento delle colonne e supporta più colonne chiave e class / attributo.

Puoi trovarlo qui: https://github.com/jumpstarter-io/colpivot

Esempio che risolve questo particolare problema:

 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; 

Questo produce:

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

Puoi risolvere questo esempio con la funzione crosstab in questo modo

 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); 

Innanzitutto, è necessario installare l’estensione tablefunc. Dalla versione 9.1 puoi farlo usando create extension:

 CREATE EXTENSION tablefunc; 

Ho scritto una funzione per generare la query dynamic. Genera lo sql per la tabella a campi incrociati e crea una vista (la elimina prima se esiste). È ansible selezionare dalla vista per ottenere i risultati.

Ecco la funzione:

 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 || '=''' || rv || ''' THEN ' || val || ' ELSE NULL END) AS ' || agr || '_' || rv; 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; 

Ed ecco come lo uso:

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

Esempio: pugno corri:

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

Di:

 Select * from ct_view; 

C’è un esempio di questo in contrib/tablefunc/ .