Sql query Cross Transform or Pivot?

Question

I have this query for MySql database:

    SELECT
    pim_pimcore_database.object_query_RGL.rulecode AS "rulecode",
    pim_pimcore_database.object_query_RGL.DescrizioneRegola AS "rule_desc",
    pim_pimcore_database.object_relations_RGL.position AS "id",
    pim_pimcore_database.objects.o_classId,
    pim_pimcore_database.objects.o_key,
    pim_pimcore_database.object_collection_ruleIF_RGL.Operatore,
    pim_pimcore_database.object_collection_ruleIF_RGL.Concatenatore
    FROM
    pim_pimcore_database.object_query_RGL
    LEFT JOIN pim_pimcore_database.object_relations_RGL
    ON pim_pimcore_database.object_query_RGL.oo_id = 
    pim_pimcore_database.object_relations_RGL.src_id 
    LEFT JOIN pim_pimcore_database.objects
    ON pim_pimcore_database.object_relations_RGL.dest_id = pim_pimcore_database.objects.o_id 
    LEFT JOIN pim_pimcore_database.object_collection_ruleIF_RGL
    ON pim_pimcore_database.object_relations_RGL.position = 
    pim_pimcore_database.object_collection_ruleIF_RGL.`index`
    AND pim_pimcore_database.object_relations_RGL.src_id = 
    pim_pimcore_database.object_collection_ruleIF_RGL.o_id
    GROUP BY
    pim_pimcore_database.object_relations_RGL.position AS "ID"
    WHERE
    pim_pimcore_database.object_query_RGL.oo_id = 1042 AND 
    pim_pimcore_database.object_relations_RGL.ownername ="IfStatement"

That gives me the result: enter image description here

But what I need is table like this: enter image description here

How do I change the query for the desired output?

I fixed your formatting - please review the changes (so you know for when you next post a question) — Caius Jard, Jun 18 '20 at 20:39
If you use a version of MySQL that supports WITH, you can WITH that whole thing as `x` and then join it to itself like `x x1 JOIN x x2 ON x1.id = x2.id AND x1.o_classid = 'OPT' and x2.o_classid = 'COM'`. ps; we normally write WHERE before GROUP BY (MySQL probbly allows you to get away with that sort of thing) and you should run your MySQL in ONLY_FULL_GROUPBY mode; this SQL groups one column but neither aggregate-funcs nor groups the others in the select list, which can lead to unexpected results — Caius Jard, Jun 18 '20 at 20:42
pivot with earlier versios you can see https://stackoverflow.com/questions/61920905/mysql-query-to-create-a-pivot-table-by-joining-4-different-table/61924128#61924128 — nbk, Jun 18 '20 at 20:47
In the absence of any aggregating functions, a GROUP BY clause is never appropriate — Strawberry, Jun 18 '20 at 21:33
Please see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Jun 18 '20 at 21:39

Caius Jard · Answer 1 · 2020-06-19T05:52:32.553

0

Your cartesian product (doubling of the rows) appears to come from joining pim_pimcore_database.objects on o_id.

If instead you represent this table twice in the query (join it twice) with relevant aliases and a predicate that selects only those kind of o_classid relevant to that alias, then the cartesian will disappear and you'll get the result you seek:

SELECT 
  ...,
  o_opt.key as OPT,
  o_com.key as COM,
  ...

LEFT JOIN pim_pimcore_database.objects o_opt ON ... AND o_opt.o_classid = 'OPT'
LEFT JOIN pim_pimcore_database.objects o_com ON ... AND o_com.o_classid = 'COM'

edited Jun 19 '20 at 05:52

answered Jun 18 '20 at 20:51

Caius Jard

72,509
5
49
80

I will try this solution :) – Mirco Cervi Jun 19 '20 at 05:30

Sql query Cross Transform or Pivot?

1 Answers1