Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Creating a cross tab / pivot table form hstore fields with dynamical column names using in postgresql
DROP TABLE IF EXISTS survey_sessions;
-- Imagine a table with survey sessions
-- token: some id or token to access a survey
-- answers: a key value store for answers
CREATE TABLE survey_sessions (
token text,
answers hstore);
-- We need some data to play with
INSERT INTO survey_sessions (token, answers) VALUES ('9IaxxP', 'a=>1, b=>2');
INSERT INTO survey_sessions (token, answers) VALUES ('TA2ZZ', 'b=>5, c=>6');
INSERT INTO survey_sessions (token, answers) VALUES ('IRQA', 'a=>7, c=>8');
-- First we need a key value view combined over all sessions
CREATE TEMP TABLE temp1 AS SELECT token, (each(answers)).key as key, (each(answers)).value as value FROM survey_sessions;
-- 1. We define a function pivotanswers that creates a pivot table with dynamic columns
-- Inspired and adapted from from Eric Minikel, - 2013-03-19
-- prerequisite: install the tablefunc module
--create extension tablefunc;
-- tablename: name of source table you want to pivot
-- rowc: the name of the column in source table you want to be the rows
-- colc: the name of the column in source table you want to be the columns
-- cellc: an aggregate expression determining how the cell values will be created
-- celldatatype: desired data type for the cells
create or replace function pivotanswers (tablename varchar, rowc varchar, colc varchar, cellc varchar, celldatatype varchar) returns varchar language plpgsql as $$
dynsql1 varchar;
dynsql2 varchar;
columnlist varchar;
-- 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 = 'CREATE TEMP TABLE results AS select * from crosstab (
''select '||rowc||','||colc||','||cellc||' from '||tablename||' group by 1,2 order by 1,2'',
''select distinct '||colc||' from '||tablename||' order by 1''
as ct (
'||rowc||' varchar,'||columnlist||'
-- Here we simply execute creating the temporary table, I could not figure out how to return a whole table
EXECUTE dynsql2;
RETURN dynsql2;
-- Now we create a pivot table with automatic column names.
SELECT pivotanswers('temp1','token','key','max(value)','text');
SELECT * from results -- Now if that's not cool, i don't know what is.

jlsydor commented May 2, 2014

That's useful stuff sir! Thanks for the insigth

easadler commented Dec 9, 2015

dynsql1 = 'select string_agg(distinct ''"''||'||colc||'||''" '||celldatatype||''','','' order by ''"''||'||colc||'||''" '||celldatatype||''') from '||tablename||';';

This will allow the column that becomes the column names to have spaces

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment