PL/SQL Group By question adding extra columns dependent on row numbers

Question

I'm struggling with a group by. I have a query which pulls two rows of data for some stock that has been counted. The rows it returns are like this.

However, I need this to display on one row like below.

This example only has two counts taking place but other examples could have up to 4 rows so would potentially need a Count 3 and Count 4 column. The count difference needs to be the last count quantity - the first rows original quantity. There is a dstamp field which can be used to identify when each count happened.

My current SQL I'm using to pull this data is below

Select bin, sku, original_qty, (original_qty + count_qty) countQty, count_difference, quantity, counter
FROM stock_counts
order by bin, dstamp DESC

Answer 1

You are not even returning dstamp in the results. But if you want to pivot, you can use conditional aggregation. It is not really clear what all the columns mean. But you can readily pivot the quantities by time using:

select bin, sku,
       max(case when seqnum = 1 then countQty end) as original_qty,
       max(case when seqnum = 2 then countQty end) as qty1,      
       max(case when seqnum = 3 then countQty end) as qty2,        
       max(case when seqnum = 4 then countQty end) as qty3 
from (select sc.*,
             row_number() over (partition by sku, bin order by dstamp) as seqnum
      from stock_counts sc
     ) sc
group by sku, bin;

Of course, you need to have enough columns to cover the number of quantities you are concerned about.