Re: CROSS-TAB query help? I have read it cant be done in one

Theo Galanakis wrote:
> Does anyone know how to perform a cross-tab query in ONE SQL without having
> to write a SP? The SQL at the end of this email attempts to display the
> subquery result-set in a cross-tab format, it does not group the content
> onto one row as it should in the sample below. SQL is below if it makes any
> sense, however the sub-query returns data as below.
>
> Examle:
>
> Name Value
> ID 1
> Cola 10
> Colb 20
> Colc 30
> Cold 40
> Cole 50
>
> I want to output as:
>
> ID, cola, colb, colb, cold, cole
> 1 10 30 30 40 50

> Actual Output:
>
> content_object_id | xpos | ypos | text | textangle | texttype
> | symbol | linktype
> -------------------+------+------+-------------------+-----------+----------
> 100473 | 93 | | | |
> 100473 | | 77 | | |
> 100473 | | | text1 | |

Don't forget the provided crosstab functions (in contrib/). If you don't
want that, you could aggregate your results:

SELECT content_object_id, MAX(xpos), MAX(ypos), ...
FROM (
<your query here>
) AS raw
GROUP BY content_object_id;

--
Richard Huxton
Archonet Ltd