Thread: CROSS-TAB query help? I have read it cant be done in one SQL, pro ve them wrong!
CROSS-TAB query help? I have read it cant be done in one SQL, pro ve them wrong!
From
Theo Galanakis
Date:
<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>
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