Insert into table_to_pivot values ( 'row3', 'col2', 32) Insert into table_to_pivot values ( 'row3', 'col1', 31) Insert into table_to_pivot values ( 'row2', 'col3', 23) Insert into table_to_pivot values ( 'row2', 'col2', 22)
Insert into table_to_pivot values ( 'row2', 'col1', 21) Insert into table_to_pivot values ( 'row1', 'col3', 13) Insert into table_to_pivot values ( 'row1', 'col2', 12) Insert into table_to_pivot values ( 'row1', 'col1', 11) retrieve list of column names.ĭynsql1 = 'select string_agg(distinct ''_''||' ||colc || '||'' ' ||celldatatype || ''','','' order by ''_''||' ||colc || '||'' ' ||celldatatype || ''') from ' ||tablename || ' ' ĭynsql2 = '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 newtable ( ' ||rowc || ' varchar,' ||columnlist || ' ) ' Įnd $$ - toy example to show how it works create table table_to_pivot ( 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 pivotcode (tablename varchar, rowc varchar, colc varchar, cellc varchar, celldatatype varchar) returns varchar language plpgsql as $$ declareīegin - 1.
#Postgresql crosstab install#
PL/pgSQL code to create pivot tables with automatic column names - Eric Minikel, - prerequisite: install the tablefunc module create extension tablefunc I run PostgreSQL 9.2 but I believe this should work at least as far back as 8.4.
But it’s better than typing out column names. That’s an extra round-trip to the database if you’re working programmatically, or an extra copy-and-paste if you’re working in the query editor. Instead this function returns the query in a varchar, and then you can execute the query to get the pivot table you want. You can’t execute the generated crosstab query automatically because PL/pgSQL functions that return tables ( setof record functions) cannot have the number and type of columns determined on the fly. To avoid spending my life typing out column names, I wrote a function in Postgres procedural language ( PL/pgSQL) that will generate a crosstab query automatically. This is called a pivot table and can be achieved in PostgreSQL using the crosstab() function, but there’s a catch: you need to type out all of the column names. Trouble is, then later if I want to do any fancy matrix math on the data I need to de-relationalize it, getting back to the rows=genes, columns=samples layout. In the above example, that would mean building a new table with three columns (gene, sample, expression_level). metadata on the samples and on the genes), I need to relationalize the table. For instance, every row is a gene, every column is a biological sample, and the cell values are the expression levels of each gene measured by microarray. Often in bioinformatics I receive a dataset that is entirely non-relational. Automatically creating pivot table column names in PostgreSQL
0 kommentar(er)
