Skip to content

@gurix /hstore_pivot.sql
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
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');
DROP TABLE IF EXISTS temp1;
-- 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, CureFFI.org - 2013-03-19
-- http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/
-- 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 $$
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 = '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
DROP TABLE IF EXISTS results;
EXECUTE dynsql2;
RETURN dynsql2;
END;
$$;
-- 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

That's useful stuff sir! Thanks for the insigth

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.