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