I need to separate a PostgreSQL table in this intercalated format-CodePudding

I'm having problems with this query in Postgres. Currently I have a table like this:

Select 
    "Charges"."saleAmount", "Charges"."buyAmount", "Operations"."id"
From 
    "Charges"
Left Join 
    "Operations" On "Operations"."id" = "Charges"."operationsId"
Order By 
    "Operations"."id"

saleAmount	buyAmount	id
200	NULL	id1
300	500	id2
0	100	id3

I need to transform it into this. Adding a new column "type" depending on "saleAmount" > 0 or "buyAmount" > 0, and separating in two rows the case when I have both saleAmount and buyAmount in the same row.

saleAmount	buyAmount	id	type
200	NULL	id1	sale
300	0	id2	sale
0	500	id2	buy
0	100	id3	buy

Could you please give me a hint of how to transform my table into this format? Of course the column "type" can be made with:

(CASE
    WHEN "saleAmount" > 0 THEN 'sale'
    WHEN "buyAmount" > 0 THEN 'buy'
 END) as "type"

But the solution of the problem is not direct... Thank you for any help!

CodePudding user response：

You can join to a values table constructor and use a case expression to determine how many rows qualify for the join:

select t.* 
from t
join (
  values(1),(2)
)x(r) on r <= case 
    when Coalesce(saleamount, 0) > 0
     and Coalesce(buyAmount, 0) > 0
    then 2 else 1 end;

CodePudding user response：

You can use UNION ALL to create two rows out of one. E.g.:

Select c."saleAmount", c."buyAmount", o."id", c.type
From 
(
  Select
    "saleAmount",
    Case When "buyAmount" > 0 Then 0 Else "buyAmount" End As "buyAmount",
    'sale' as type
  From "Charges"
  Where "saleAmount" > 0
  Union All
  Select
    Case When "saleAmount" > 0 Then 0 Else "saleAmount" End As "saleAmount",
    "buyAmount",
    'buy' as type
  From "Charges"
  Where "buyAmount" > 0
) c
Left Join "Operations" o On o."id" = c."operationsId"
Order By o."id";

The join of the Operations table seems superfluous by the way. Either the Charges has an operationsId, then it links to an Operations row with the same ID or it doesn't have an operationsId, then it doesn't link to Operations row. So why not just show the "Charges"."operationsId" instead of joining to the Operations table just to show the same ID?

CodePudding user response：

I think you can use union all.

select
  c.saleAmount,
  0 buyAmount,
  o.id,
  'sale'
from Charges c
  Left Join "Operations" o On o."id" = c."operationsId"
where isnull(c.saleAmount) > 0
union all
select
  0 saleAmount,
  c.buyAmount,
  o.id,
  'buy'
from Charges c
  Left Join "Operations" o On o."id" = c."operationsId"
where isnull(c.buyAmount,0) > 0

CodePudding user response：

In Postgres you can unpivot the two columns to rows with values and a lateral join, then filter out unwanted rows in the where clause:

select c.*, t.type
from charges c
cross join lateral ( 
    values (c.saleamount, 'sale'), (c.buyamount, 'buy')
) t(amount, type)
where t.amount > 0

It is not obvious what the purpose of the left join in the original query is, so I left it apart - but you can easily add it to the query if needed.