PostgreSQL 9.3:Dynamic Cross tab query

Question

I am really new to PostgreSQL using version is 9.3.

Well I have the following table with some records:

Example:

create table tst
(
cola varchar(10),
colc varchar(10)
)

insert into tst values('101','A1');
insert into tst values('101','A2');
insert into tst values('201','A3');
insert into tst values('301','A4');
insert into tst values('401','A1');
insert into tst values('101','A6');
insert into tst values('201','A1');
insert into tst values('201','A5');

Note: Now I want to show only that records in which cola belongs to colc's values. If the user pass the colc values as a parameter to function then it has to match the exact value of colc belongs to which cola value.

Expected Result:

If the user pass A1,A2,A6 then the result should be:

cola   A1   A2   A6
--------------------
101    1    1    1

Note: In the above result the record 101 appears because it belongs to A1,A2,A6 not other values. 201 not appear because that belongs to A1,A3 and A5 also.

If the user pass A1 then the result should be:

cola   A1 
----------
401    1

Note: In the above result the record 401 appears because it belongs to only A1.

I am not getting how to write crosstab within function for this scenario.

refer this answer http://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905 — Vivek S., Nov 25 '14 at 06:04

score 1 · Accepted Answer · answered Nov 26 '14 at 02:39

A server-side function cannot have a dynamic return type in PostgreSQL, so obtaining the mentioned result as-is from a fixed function is not possible.

Also, it does not look much like a typical crosstab problem, anyway. The cola part of the output can be obtained by filtering over an aggregate, and the other columns A1/A2/A6 are actually the input, so copying them as columns into the output is easy in the context of a client-side generated query.

The gist of an actual SQL query finding the matching rows would be:

select cola from ts
group by cola
having array_agg(colc order by colc)='{A1,A2,A6}'

This will find 101.

Adding the other columns is a just a client-side presentation problem. For instance, the query could be written like this:

select cola, 1 as A1, 1 as A2, 1 as A6 from tst
group by cola
having array_agg(colc order by colc)='{A1,A2,A6}';

result:

 cola | a1 | a2 | a6 
------+----+----+----
 101  |  1 |  1 |  1

PostgreSQL 9.3:Dynamic Cross tab query

1 Answers1