How to pivot or crosstab in postgresql without writing a function?

postgresql pivot crosstab
samthebrand picture samthebrand · May 11, 2018 · Viewed 8.7k times · Source

I have a dataset that looks something like this:

gotta pivot

I'd like to aggregate all co values on one row, so the final result looks something like:

enter image description here

Seems pretty easy, right? Just write a query using crosstab, as suggested in this answer. Problem is that requires that I CREATE EXTENSION tablefunc; and I don't have write access to my DB.

Can anyone recommend an alternative?

Answer

Lukasz Szozda picture Lukasz Szozda · May 11, 2018

Conditional aggregation:

SELECT co,
  MIN(CASE WHEN ontology_type = 'industry' THEN tags END) AS industry,
  MIN(CASE WHEN ontology_type = 'customer_type' THEN tags END) AS customer_type, 
  -- ...
FROM tab_name
GROUP BY co

Related questions

PostgreSQL Crosstab Query

Does any one know how to create crosstab queries in PostgreSQL? For example I have the following table: Section Status Count A Active 1 A Inactive 2 B Active 4 B Inactive 5 I would like the query to return the following crosstab: Section …

Read More
Dynamic alternative to pivot with CASE and GROUP BY

I have a table that looks like this: id feh bar 1 10 A 2 20 A 3 3 B 4 4 B 5 5 C 6 6 D 7 7 D 8 8 D And I want it to look like this: bar val1 val2 val3 A 10 20 B 3 4 C 5 D 6 7 8 I have this query that …

Read More
Pivot on Multiple Columns using Tablefunc

Has anyone used tablefunc to pivot on multiple variables as opposed to only using row name? The documentation notes: The "extra" columns are expected to be the same for all rows with the same row_name value. I'm not sure …

Read More