I am working with a particularly annoying dataset. I have just managed to get the data to align with what is expected but now I am having trouble with merging/combining some rows so that I have a neater table. To help you understand this is what I am getting:

And this is what I desire:

and this is the bottom of my current query:

SELECT  
order_id
,NULL AS aso_flag
,dpo_flag
,tcv
,0 AS ops
,0 AS penalty_charge

FROM CO

UNION ALL

SELECT 
order_id
,aso_flag
,null AS dpo_flag
,0 AS tcv
,ops
,penalty_charge

FROM OPS  

The initial tables used don't mirror each other exactly (missing order_ids) and a full join on them seems to be very very slow. Any suggestions would be much appreciated!

CodePudding user response：

Derived Table from your Union then group by and `max'

SELECT
  order_id,
  MAX(aso_flag) aso_flag,
  MAX(dpo_flag) dpo_flag,
  MAX(tcv) tcv,
  MAX(ops) ops,
  MAX(penalty_charge) penalty_charge
FROM (SELECT
  order_id,
  NULL AS aso_flag,
  dpo_flag,
  tcv,
  0 AS ops,
  0 AS penalty_charge

FROM CO

UNION ALL

SELECT
  order_id,
  aso_flag,
  NULL AS dpo_flag,
  0 AS tcv,
  ops,
  penalty_charge

FROM OPS) x
GROUP BY order_id