CROSS-TAB query help? I have read it cant be done in one SQL, pro ve them wrong! - Mailing list pgsql-sql

<p><font size="2">Does anyone know how to perform a cross-tab query in ONE SQL without having to write a SP? The SQL at
theend of this email attempts to display the subquery result-set in a cross-tab format, it does not group the content
ontoone row as it should in the sample below. SQL is below if it makes any sense, however the sub-query returns data as
below.</font><p><fontsize="2">Examle:</font><p><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><p><font size="2">I want to output
as:</font><p><fontsize="2">ID, cola, colb, colb, cold, cole</font><br /><font size="2">1       10      30      30     
40     50</font><p><font size="2">This is how it should output:</font><p><font size="2">content_object_id | xpos | ypos
|      text        | textangle |  texttype  |     symbol     | linktype</font><br /><font
size="2">-------------------+------+------+-------------------+-----------+------------+----------------+----------</font><br
/><fontsize="2">            100473 | 93   | 77   |   text1           | 0             |  txt-pop3 |
pop1_att              | optional</font><br /><p><font size="2">Actual Output:</font><p><font
size="2"> content_object_id| xpos | ypos |       text        | textangle |  texttype  |     symbol     |
linktype</font><br/><font
size="2">-------------------+------+------+-------------------+-----------+------------+----------------+----------</font><br
/><fontsize="2">            100473 | 93   |      |                   |           |            |               
|</font><br/><font size="2">            100473 |      | 77   |                   |           |           
|               |</font><br /><font size="2">|</font><br /><font size="2">            100473 |      |      |
text1           |           |            |                |</font><br /><font size="2">            100473 |      |     
|                  | 0         |            |                |</font><br /><font size="2">            100473 |     
|     |                   |           | txt-pop3   |                |</font><br /><font size="2">            100473
|     |      |                   |           |            |                 pop1_att|</font><br /><font
size="2">SQL:</font><br/><p><font size="2">select distinct mapInfo.content_object_id, </font><br />        <font
size="2">CASEWHEN mapInfo.node_id = 6957 THEN mapInfo.content END as xPos,</font><br />        <font size="2">CASE WHEN
mapInfo.node_id= 6958 THEN mapInfo.content END as yPos,</font><br />        <font size="2">CASE WHEN mapInfo.node_id =
6959THEN mapInfo.content END as text,</font><br />        <font size="2">CASE WHEN mapInfo.node_id = 6960 THEN
mapInfo.contentEND as textAngle,</font><br />        <font size="2">CASE WHEN mapInfo.node_id = 6961 THEN
mapInfo.contentEND as textType,</font><br />        <font size="2">CASE WHEN mapInfo.node_id = 6962 THEN
mapInfo.contentEND as symbol,</font><br />        <font size="2">CASE WHEN mapInfo.node_id = 6963 THEN mapInfo.content
ENDas linkType</font><br /><font size="2">from (  SELECT child_node_names.node_id,
child_content_facts.content_object_id,child_node_names.node_name,</font><br />                        <font
size="2">CASEWHEN child_node_names.node_id = 6962 THEN </font><br />                                <font
size="2">(selectnode_name from node_names where node_id = content_fact_versions.content)</font><br />               
       <font size="2">WHEN child_node_names.node_id = 6961 THEN</font><br />                                <font
size="2">(selectnode_name from node_names where node_id = content_fact_versions.content)</font><br />               
       <font size="2">WHEN child_node_names.node_id = 6963 THEN</font><br />                                <font
size="2">(selectnode_name from node_names where node_id = content_fact_versions.content)</font><br />               
       <font size="2">ELSE </font><br />                                <font
size="2">content_fact_versions.content</font><br/>                        <font size="2">END</font><br />       
               <font size="2">FROM content_objects_join</font><br />                        <font size="2">INNER JOIN
content_objectsas parent_content_objects ON parent_content_objects.content_object_id =
content_objects_join.parent_cobj_id</font><p>                               <font size="2">INNER JOIN nodes as
parent_nodesON parent_nodes.node_id = parent_content_objects.node_id</font><p>                                       
<fontsize="2">INNER JOIN node_names as parent_node_names ON parent_node_names.node_id =
parent_nodes.node_id</font><p>                       <font size="2">INNER JOIN content_facts as child_content_facts ON
child_content_facts.content_object_id= content_objects_join.child_cobj_id</font><p>                               
<fontsize="2">INNER JOIN content_fact_versions ON content_fact_versions.fact_id =
child_content_facts.fact_id</font><p>                               <font size="2">INNER JOIN node_names as
child_node_namesON child_node_names.node_id = child_content_facts.node_id</font><p>                        <font
size="2">WHEREparent_nodes.node_id = 341</font><br />                        <font size="2">--AND
parent_content_objects.object_type_idin (1,2,3,4,6,8,9)</font><br />                        <font size="2">AND
parent_content_objects.object_type_id= 73</font><br />                        <font size="2">ORDER BY
child_content_facts.content_object_id</font><br/>        <font size="2">) as mapInfo </font><br /><font size="2">group
bymapInfo.content_object_id,  xPos, yPos, text, textAngle, textType, symbol, linkType</font><table><tr><td
bgcolor="#ffffff"><fontcolor="#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
theintended recipient of this email, you must not<br />use, copy or disclose its content to anyone. You must not copy
or<br />communicate to others content that is confidential or subject to <br />copyright, unless you have the consent
ofthe content owner.<br /></font></td></tr></table>