Question 90225 in PostgreSQL Question posted in
The official documentation can be found here.

I need to separate a PostgreSQL table in this intercalated format

I have this query in Postgres:

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: Add a new column type depending on saleAmount > 0 or buyAmount > 0, and separate into two rows 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

How do I transform my table into this format?

Column type can be made with:

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

4

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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?

    Login or Signup to reply.
  4. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search