Interbase SQL remake a Select query

Question

I need you help.

I have an Interbase SQL Select query:

Select aps.fullname, aps.productvcode, aps.alccode, aps.capacity, Cast(Round(Sum(case when wp.capacity>0 then wp.quantity * wp.capacity/10 else wp.quantity * wp.capacity end),4) as DECIMAL(18,4)) as WBTotal from AP aps left join Waybill_positions wp on wp.alccode=aps.alccode where wp.alccode='0001821000001389010' group by aps.fullname, aps.productvcode, aps.alccode, aps.capacity
union all
Select aps.fullname, aps.productvcode, aps.alccode, aps.capacity, Cast(Round(Sum(case when wp.capacity>0 then wp.quantity * wp.capacity/10 else wp.quantity * wp.capacity end),4) as DECIMAL(18,4)) as WBTotal from AP aps left join Waybill_out_positions wp on wp.alccode=aps.alccode where wp.alccode='0001821000001389010' group by aps.fullname, aps.productvcode, aps.alccode, aps.capacity

And it gives me this:

But I want the value "156,9750" from the second row to be a separate column on the right beside "WBTOTAL" and have the title "WBOTOTAL"

How to do it?

Answer 1

You can use CTE, like below

with CTE1 as
(
    Select aps.fullname, aps.productvcode, aps.alccode, aps.capacity, Cast(Round(Sum(case when wp.capacity>0 then wp.quantity * wp.capacity/10 else wp.quantity * wp.capacity end),4) as DECIMAL(18,4)) as WBTotal from AP aps left join Waybill_positions wp on wp.alccode=aps.alccode where wp.alccode='0001821000001389010' group by aps.fullname, aps.productvcode, aps.alccode, aps.capacity
),
CTE2 as
(
    Select aps.fullname, aps.productvcode, aps.alccode, aps.capacity, Cast(Round(Sum(case when wp.capacity>0 then wp.quantity * wp.capacity/10 else wp.quantity * wp.capacity end),4) as DECIMAL(18,4)) as WBTotal from AP aps left join Waybill_out_positions wp on wp.alccode=aps.alccode where wp.alccode='0001821000001389010' group by aps.fullname, aps.productvcode, aps.alccode, aps.capacity
)
Select CTE1.fullname, CTE1.productvcode, CTE1.alccode, CTE1.capacity, CTE1.WBTotal, CTE2.WBTotal as WBOTOTAL
From  CTE1 
join  CTE2
on CTE1.productvcode = CTE2.productvcode
and CTE1.alccode = CTE2.alccode