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

Question

I have a dataset that looks something like this:

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

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 1

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

Answer 2

You can use DO to generate and PREPARE your own SQL with crosstab columns, then EXECUTE it.

-- replace tab_name to yours table name

DO $$
DECLARE
  _query text;
  _name text;
BEGIN
  _name := 'prepared_query';
  _query := '
    SELECT co
        '||(SELECT ', '||string_agg(DISTINCT 
                    ' string_agg(DISTINCT 
                                CASE ontology_type WHEN '||quote_literal(ontology_type)||' THEN tags 
                                ELSE NULL 
                                END, '',''
                                ) AS '||quote_ident(ontology_type),',') 
            FROM tab_name)||'
    FROM tab_name
    GROUP BY co
    ';

    BEGIN
        EXECUTE 'DEALLOCATE '||_name;
    EXCEPTION
        WHEN invalid_sql_statement_name THEN
    END;

    EXECUTE 'PREPARE '||_name||' AS '||_query;
END
$$;

EXECUTE prepared_query;

Answer 3

By using pivot also we can achieve your required out put

SELECT co
    ,industry
    ,customer_type
    ,product_type
    ,sales_model
    ,stage
FROM dataSet
PIVOT(max(tags) FOR ontologyType IN (
            industry
            ,customer_type
            ,product_type
            ,sales_model
            ,stage
            )) AS PVT