How to do Pivoting/crosstab in PostgreSQL with dynamic field

Question

I am new to PostgreSQL.

id | customer_id | form_id | field_id | field_name | form_submission_id |    value     |
---+-------------+---------+----------+------------+--------------------+--------------+
 1 |           2 |       7 | c313     | Program    |                  1 | 2013         |            
 2 |           2 |       7 | c313     | Program    |                  2 | PIP          |            
 3 |           2 |       7 | c313     | Program    |                  3 | CIP          |            
 4 |           2 |       7 | c343     | Broker     |                  1 | broker test  |
 5 |           2 |       7 | c343     | Broker     |                  2 | broker test1 |            
 6 |           2 |       7 | c343     | Broker     |                  3 | broker test2 |            
 7 |           2 |       7 | c339     | Class      |                  1 | Class test   |             
 8 |           2 |       7 | c339     | Class      |                  2 | Class test1  |            
 9 |           2 |       7 | c339     | Class      |                  3 | Class test2  |

I want such record as

customer_id form_id Program      Broker        Class        form_submission_id 
  2          7        2013       broker test   Class test        1
  2          7        PIP        broker test1  Class test1       1
  2          7        CIP        broker test2  Class test3       1

field_name value will be dynamic, not fixed value.

I have try this but getting errors like 'ERROR: return and sql tuple descriptions are incompatible'

select * from crosstab (
    'select Distinct customer_id ,form_id , field_name from form_submissions_reports '
    )
    as newtable (
     customer_id integer,form_id integer,field_id1 varchar,field_id2 varchar,field_id3 varchar
    );

But Important it field name is dynamic.

Yeah, right. The relevant contrib is the first result, and the relevant StackOverflow dup comes bext: http://stackoverflow.com/questions/3002499/postgresql-crosstab-query — Denis de Bernardy, Dec 16 '13 at 13:14
But it has fixed value of status. I don't have fixed value of field_name — Shankar Kamble, Dec 16 '13 at 13:17
Then edit your question and explain what you tried and *why* the stuff in that question didn't work. — Denis de Bernardy, Dec 16 '13 at 13:18
Strongly suggest that you do the crosstabbing outside of the DB, in your application. Much faster and easier that way — Shamps, Dec 19 '13 at 15:36
Table after "I want such record as", column "form_submission_id" values should be "1, 2, 3" instead of "1, 1, 1". It makes no sense to be "1, 1, 1" in your case. I already edited it, but it was rejected. — Milos, Dec 20 '13 at 14:33

Milos · Answer 1 · 2013-12-19T14:57:53.740

To setup an example:

create table form_submissions_reports (id integer, customer_id integer, 
form_id integer, field_id text, field_name text, form_submission_id integer,
"value" text);


insert into form_submissions_reports
select 1 ,2 ,7 , 'c313', 'Program',1 , '2013' union all
select 2 ,2 ,7 , 'c313', 'Program',2 , 'PIP' union all
select 3 ,2 ,7 , 'c313', 'Program',3 , 'CIP' union all
select 4 ,2 ,7 , 'c343', 'Broker',1 , 'broker test' union all
select 5 ,2 ,7 , 'c343', 'Broker',2 , 'broker test1' union all
select 6 ,2 ,7 , 'c343', 'Broker',3 , 'broker test2' union all
select 7 ,2 ,7 , 'c339', 'Class',1 , 'Class test' union all
select 8 ,2 ,7 , 'c339', 'Class',2 , 'Class test1' union all
select 9 ,2 ,7 , 'c339', 'Class',3 , 'Class test2';

This is the solution with fixed number of columns you tried:

select * 
from crosstab (
'select Distinct form_submission_id , customer_id ,form_id , field_name, value 
from form_submissions_reports order by form_submission_id ', 
'select Distinct field_name from form_submissions_reports'
)as newtable (
              form_submission_id integer, customer_id integer,form_id integer,field_id 
              varchar,field_id2 varchar,field_id3 varchar
              );

Relational databases, as far as my experience goes, are not built for types that are not fixed in number of columns and their types. So you have to cheat.

This is only good for amusement. I don't see any production value in this, fully "dynamic", solution (flexible number of columns, data driven names):

1) Create these two functions:

CREATE OR REPLACE FUNCTION foo()
RETURNS text as
$BODY$
DECLARE
dynamic_columns text;
BEGIN

select array_to_string(array_agg(distinct  field_name||' text'), ', ') into dynamic_columns from form_submissions_reports;

return 'select * from crosstab (
        ''select Distinct form_submission_id , customer_id ,form_id , field_name, value from form_submissions_reports order by form_submission_id '', 
    ''select Distinct field_name from form_submissions_reports''
    )
    as newtable (
     form_submission_id integer, customer_id integer,form_id integer, '|| dynamic_columns ||'
    )';


END;
$BODY$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION bar()
RETURNS void as
$BODY$
DECLARE
dyn_crosstab text;
BEGIN

DROP VIEW IF EXISTS barview;

select foo() into dyn_crosstab;

execute 'create view barview as '||dyn_crosstab;

END;
$BODY$
LANGUAGE plpgsql;

2) Execute bar() function. This will give you the new version of "barview" view. After that, query the barview.

select bar();
select * from barview;

3) To test if it's dynamic, insert a new row with different value for field_name, and then repeat 2):

INSERT INTO form_submissions_reports
(
  id,  customer_id,  form_id,  field_id,
  field_name,  form_submission_id,  value
)
VALUES(  10,  2,  7,  'd',  'NEWFIELD',  4,
  'newfield test');

select bar();
select * from barview;

How to do Pivoting/crosstab in PostgreSQL with dynamic field

1 Answers1