How to create a local table inside a Postgres query-CodePudding

I am trying to create a local table inside of a Postgres query that I am currently running. I have tried to create a new table in a join but it just adds a line the result set.

My end goal is to have each input number aline with the output number in each Machine Run. Currently it shows the input on one line and all of its outputs below it.

Is it possible to create a table inside of this query and I can add a Else statement to pull data from the table I created inside of the query?

Here is a sample of my code:

SELECT 

"MaterialType"."name",
concat('Run #', "MachineRun"."id")                                            AS "Machine Run",
CASE WHEN "RunMaterial"."is_input" = TRUE THEN "Tag"."uid"
    ELSE NULL END                                                             AS "(Input)",
CASE WHEN "RunMaterial"."is_input" = FALSE THEN "Tag"."uid"
    ELSE NULL END                                                             AS "(Output)",
FROM tenant_tenant "Tenant"
    INNER JOIN core_machinerun "MachineRun"
        ON "Tenant"."id" = "MachineRun"."tenant_id"
    INNER JOIN core_runmaterial "RunMaterial"
        ON "MachineRun"."id" = "RunMaterial"."machine_run_id"
    INNER JOIN core_material "Material"
        ON "RunMaterial"."material_id" = "Material"."id" 
    INNER JOIN core_machine "Machine"
        ON "MachineRun"."machine_id" = "Machine"."id"
    INNER JOIN integrations_tag "Tag"
        ON "Material"."tag_id" = "Tag"."id"
    INNER JOIN core_materialtype "MaterialType"
        ON "Material"."material_type_id" = "MaterialType"."id"

Below are the images of my current return and the goal I am looking for.

On the left is what I need my results to be, If I change my CASE WHEN for my INPUT column, the result is on the right.

CodePudding user response：

From your edit answer, you can try to use MAX window function to make it as below (T is your query in your question.)

SELECT MachineRun,MAX(input) OVER(PARTITION BY MachineRun),output
FROM T

so it might look like that.

SELECT 
    "MaterialType"."name",
    concat('Run #', "MachineRun"."id")                                            AS "Machine Run",
    MAX(CASE WHEN "RunMaterial"."is_input" = TRUE THEN "Tag"."uid"
        ELSE NULL END)  OVER  (PARTITION BY "MachineRun"."id")                    AS "(Input)",
    CASE WHEN "RunMaterial"."is_input" = FALSE THEN "Tag"."uid"
        ELSE NULL END                                                             AS "(Output)"
FROM tenant_tenant "Tenant"
    INNER JOIN core_machinerun "MachineRun"
        ON "Tenant"."id" = "MachineRun"."tenant_id"
    INNER JOIN core_runmaterial "RunMaterial"
        ON "MachineRun"."id" = "RunMaterial"."machine_run_id"
    INNER JOIN core_material "Material"
        ON "RunMaterial"."material_id" = "Material"."id" 
    INNER JOIN core_machine "Machine"
        ON "MachineRun"."machine_id" = "Machine"."id"
    INNER JOIN integrations_tag "Tag"
        ON "Material"."tag_id" = "Tag"."id"
    INNER JOIN core_materialtype "MaterialType"
        ON "Material"."material_type_id" = "MaterialType"."id"

sqlfiddle