Transposing an sql result so that one column goes onto multiple columns

by

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. Don’t use it as column name! I renamed it to usr.

You need to install the additional module tablefunc which provides the function crosstab(). Note that this operation is strictly per database.
In PostgreSQL 9.1 you can simply:

CREATE EXTENSION tablefunc;

For older version you would execute a shell-script supplied in your contrib directory. In Debian, for PostgreSQL 8.4, that would be:

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

More Related Contents:

  1. PostgreSQL Crosstab Query
  2. Dynamic alternative to pivot with CASE and GROUP BY
  3. Pivot on Multiple Columns using Tablefunc
  4. Create a pivot table with PostgreSQL
  5. Transpose latest rows per user to columns
  6. PostgreSQL convert columns to rows? Transpose?
  7. PostgreSQL 9.3: Dynamic pivot table
  8. Crosstab with a large or undefined number of categories
  9. Select first row in each GROUP BY group?
  10. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  11. How to use RETURNING with ON CONFLICT in PostgreSQL?
  12. PostgreSQL DISTINCT ON with different ORDER BY
  13. Escaping keyword-like column names in Postgres
  14. Delete duplicate rows (don’t delete all duplicate)
  15. GROUP BY + CASE statement
  16. subquery in FROM must have an alias
  17. Compute percents from SUM() in the same SELECT sql query
  18. SQL Between clause with strings columns
  19. Insert an image in postgresql database
  20. Perform this hours of operation query in PostgreSQL
  21. Getting date list in a range in PostgreSQL
  22. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  23. How to use an ALIAS in a PostgreSQL ORDER BY clause?
  24. Postgresql Column Not Found, But Shows in Describe
  25. PostgreSQL does not accept column alias in WHERE clause
  26. INSERT COMMAND :: ERROR: column “value” does not exist
  27. Return multiple columns and rows from a function PostgreSQL instead of record
  28. SQL LIKE condition to check for integer?
  29. Postgresql query between date ranges
  30. Why isn’t row level security enabled for Postgres views?

Leave a Comment