SQL snippet to create a dynamic pivot table. Adapted from http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/

pivotcode.sql

DROP FUNCTION IF EXISTS pivotcode(varchar, varchar, varchar, varchar, varchar, varchar);
create or replace function pivotcode (tablename varchar, resultname varchar, rowc varchar, colc varchar, cellc varchar, celldatatype varchar) 
returns VOID language plpgsql as $$
declare
    dynsql1 varchar;
    dynsql2 varchar;
    columnlist varchar;
begin
    -- 1. retrieve list of column names.
    dynsql1 = 'select string_agg(distinct ''"''||'||colc||'||''" '||celldatatype||''','','' order by ''"''||'||colc||'||''" '||celldatatype||''') from '||tablename||';';
    execute dynsql1 into columnlist;
    -- 2. set up the crosstab query
    dynsql2 = 'DROP TABLE IF EXISTS pivot_results;
	           CREATE TEMPORARY TABLE pivot_results AS(
                       select * from crosstab (
                       ''select '||rowc||','||colc||','||cellc||' from '||tablename||' order by 1,2'',
                       ''select distinct '||colc||' from '||tablename||' order by 1''
                        ) as newtable ('||rowc||' varchar,'||columnlist||')
                   );'
    ;
    EXECUTE dynsql2;
end
$$