SQL Transpose Rows as Columns

Use:

  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_idGROUP BY r.user_id

This is a standard pivot query, because you are "pivoting" the data from rows to columnar data.

I implemented a truly dynamic function to handle this problem without having to hard code any specific class of answers or use external modules/extensions. It also gives full control over column ordering and supports multiple key and class/attribute columns.

You can find it here: https://github.com/jumpstarter-io/colpivot

Example that solves this particular problem:

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;

This outputs:

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

You can solve this example with the crosstab function in this way

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

First, you must install tablefunc extension. Since 9.1 version you can do it using create extension:

CREATE EXTENSION tablefunc;