CROSS-TAB query help? I have read it cant be done in one SQL, pro ve them wrong!
| От | Theo Galanakis |
|---|---|
| Тема | CROSS-TAB query help? I have read it cant be done in one SQL, pro ve them wrong! |
| Дата | |
| Msg-id | 82E30406384FFB44AFD1012BAB230B55037D0504@shiva.au.lpint.net обсуждение исходный текст |
| Ответы |
Re: CROSS-TAB query help? I have read it cant be done in one
(Richard Huxton <dev@archonet.com>)
|
| Список | 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>
В списке pgsql-sql по дате отправления: