Re: CROSS-TAB query help? I have read it cant be done in on - Mailing list pgsql-sql

<p><font size="2">Thanks Rickard </font><br /><font size="2">Max may not work as not all the data is numerical. However
Iwill give the contrib/cross-tab a go!</font><p><font size="2">Theo</font><br /><font size="2">-----Original
Message-----</font><br/><font size="2">From: Richard Huxton [<a
href="mailto:dev@archonet.com">mailto:dev@archonet.com</a>]</font><br /><font size="2">Sent: Monday, 16 August 2004
6:06PM</font><br /><font size="2">To: Theo Galanakis</font><br /><font size="2">Cc:
'pgsql-sql@postgresql.org'</font><br/><font size="2">Subject: Re: [SQL] CROSS-TAB query help? I have read it cant be
donein one SQL, pro ve them wrong!</font><br /><p><font size="2">Theo Galanakis wrote:</font><br /><font size="2">>
Doesanyone know how to perform a cross-tab query in ONE SQL without </font><br /><font size="2">> having to write a
SP?The SQL at the end of this email attempts to </font><br /><font size="2">> display the subquery result-set in a
cross-tabformat, it does not </font><br /><font size="2">> group the content onto one row as it should in the sample
below.SQL </font><br /><font size="2">> is below if it makes any sense, however the sub-query returns data as
</font><br/><font size="2">> below.</font><br /><font size="2">> </font><br /><font size="2">>
Examle:</font><br/><font size="2">> </font><br /><font size="2">> Name  Value</font><br /><font size="2">>
ID   1</font><br /><font size="2">> Cola  10</font><br /><font size="2">> Colb  20</font><br /><font
size="2">>Colc  30</font><br /><font size="2">> Cold  40</font><br /><font size="2">> Cole  50</font><br
/><fontsize="2">> </font><br /><font size="2">> I want to output as:</font><br /><font size="2">> </font><br
/><fontsize="2">> ID, cola, colb, colb, cold, cole</font><br /><font size="2">> 1     10      30      30     
40     50</font><p><font size="2">> Actual Output:</font><br /><font size="2">> </font><br /><font size="2">> 
content_object_id| xpos | ypos |       text        | textangle |  texttype</font><br /><font size="2">> |    
symbol    | linktype</font><br /><font size="2">>
-------------------+------+------+-------------------+-----------+----</font><br/><font size="2">>
-------------------+------+------+-------------------+-----------+----</font><br/><font size="2">>
-------------------+------+------+-------------------+-----------+--</font><br/><font size="2">>             100473
|93   |      |                   |           |</font><br /><font size="2">>             100473 |      | 77  
|                  |           |</font><br /><font size="2">>             100473 |      |      | text1           
|          |</font><p><font size="2">Don't forget the provided crosstab functions (in contrib/). If you don't
</font><br/><font size="2">want that, you could aggregate your results:</font><p><font size="2">SELECT
content_object_id,MAX(xpos), MAX(ypos), ...</font><br /><font size="2">FROM (</font><br /><font size="2"><your query
here></font><br/><font size="2">) AS raw</font><br /><font size="2">GROUP BY content_object_id;</font><p><font
size="2">--</font><br /><font size="2">   Richard Huxton</font><br /><font size="2">   Archonet
Ltd</font><table><tr><tdbgcolor="#ffffff"><font
color="#000000">______________________________________________________________________<br/>This email, including
attachments,is intended only for the addressee<br />and may be confidential, privileged and subject to copyright. If
you<br/>have received this email in error, please advise the sender and delete<br />it. If you are not the intended
recipientof this email, you must not<br />use, copy or disclose its content to anyone. You must not copy or <br
/>communicateto others content that is confidential or subject to <br />copyright, unless you have the consent of the
contentowner.<br /></font></td></tr></table>