Am 24.04.2012 22:08, schrieb Samuel Gendler:
On Tue, Apr 24, 2012 at 1:01 PM, Andreas <maps...@gmx.net
<mailto:maps...@gmx.net>> wrote:
Hi,
is there a generic solution to dump the result of a query as a
crosstab, when I can't know how many columns I will need?
E.g. I get something like this:
id, x
1, a
1, b
1, c
2, l
2, m
Yes. You can provide a query which returns the columns to the version
of the crosstab function which looks like this:
|crosstab(text source_sql, text category_sql)|
It does exactly what you are looking for. The second query returns the
set of values that act as columns in the final result (the pivot for
each row in the result returned by the first query). This allows the
function to correctly insert a null for any column for which there is
no row in the first query results.
I got stuck with an error that translates to "Materialisation mode is
needed but is not allowed in this context."
I couldn't figure out what this materialisation mode is, yet.
Could you please have a look at my query sample?
Both queries work for themselves but crosstab() fails. :(
I checked and there are never more than 20 child_ids per parent_id so
there should be enough columns.
select
crosstab (
$$
select
parent_id as row_name,
'x' || row_number() over ( partition by parent_id order by
child_id ) as category,
child_id as value
from
children
order by 1
$$,
$$
select 'x' || generate_series(1, 20) as cat order by 1
$$
);