postgresqlpivotcrosstab

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

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?

Solution

  • 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