Transpose the sql result so that one column jumps to multiple columns

Question

I am trying to get data from a table for a survey in a specific format. However, all my attempts seem to pass the DB due to too many joins / too heavy for the DB.

My data is as follows:

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 

There are about 250,000 lines, and each user has about 30 lines. I want the result to look like this:

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

So, each user has one row as a result, each of which has a separate column for each answer.

I use Postgres, but answers in any SQL language will be appreciated, since I could translate to Postgres.

EDIT: I also need to deal with users not answering questions, i.e. in the example above q2 for user 1.

Answer 1

Consider the following demo:

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

Result:

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

user is a reserved word . Do not use it as a column name! I renamed it to usr .

You need to install the tablefunc add-on module, which provides the crosstab() function. Please note that this operation is performed strictly for the database. In PostgreSQL 9.1, you can simply:

 CREATE EXTENSION tablefunc; 

For an older version, you must run the shell script specified in the contrib directory. On Debian for PostgreSQL 8.4, this will be:

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

Answer 2

Erwins answer is good until a missed answer appears for the user. I'm going to make an assumption about you .... you have a user table that has one row for each user, and you have a question table in which there is one row for each question.

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

This statement will create a line for each user / question and will be in the same order. Turn it into a subquery and paste it into your data ...

 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 

Paste this into the Erwins crosstab statement and give him credit for the answer: P

Answer 3

I implemented a truly dynamic function to solve this problem without the need for hard coding of any certain number of questions or using external modules / extensions. It is also much easier to use than crosstab() .

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

An example that solves this specific problem:

 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; 

Result:

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